How to Fix

Morris, Tyler 0 Reputation points
2026-11-15T22:36:19.81+00:00

How to fix #N/A on the What if simulation. I ran the simulation correctly using the Data table function on the what if analysis and it still won't work.

Microsoft 365 and Office | Excel | For business | Other
0 comments No comments

2 answers

Sort by: Most helpful
  1. Aetherin 4,500 Reputation points Independent Advisor
    2026-11-15T22:50:26.5233333+00:00

    Hi @Morris, Tyler,

    To help me better understand the issue and narrow down the cause, could you please confirm the following?

    • Does the original result formula return a valid value before you create the Data Table, or does it already return #N/A?
    • Are you creating a one-variable or two-variable Data Table? If possible, please share a screenshot showing the table layout and the selected Row input cell and/or Column input cell.
    • Does the issue occur in a new workbook as well when using a simple test formula?

    In the meantime, please check the following:

    • Make sure the top cell of the Data Table contains a direct reference to the result cell, for example =B10, and verify that the Row input cell and/or Column input cell points to the actual input cell used by the formula.
    • Go to Formulas > Calculation Options and ensure Automatic is selected. Then press Ctrl + Alt + F9 to force a full workbook recalculation.

    You can also compare your setup with Microsoft’s guidance: Calculate multiple results by using a data table.

    If there are any updates, please post them in the comments section so I can receive a notification and continue assisting you. I look forward to your response.

    Thank your for taking the time.

    Was this answer helpful?


  2. AI answer

    2026-11-15T22:37:03.9566667+00:00

    Test the Data Table’s source formula with each input value manually, because a Data Table repeats that formula and will propagate any #N/A produced by the underlying calculation.

    1. Isolate the formula from the Data Table

    1. Note the cell specified as the Row input cell or Column input cell.
    2. Temporarily enter one of the simulation values directly into that input cell.
    3. Inspect the original output formula—the formula linked from the corner/header of the Data Table.
    4. Repeat with a value whose table result currently shows #N/A.

    If the original output also becomes #N/A, fix that formula or its source data first. #N/A generally means a formula cannot find the requested value. For lookup formulas such as XLOOKUP, VLOOKUP, or MATCH, check that:

    • The lookup value exists.
    • The lookup value and source value have the same data type—for example, both are numbers rather than one number and one text value.
    • Referenced cells do not already contain #N/A or NA().

    Use Ctrl+1 → Number to compare formats. If an entire column contains numbers stored as text, set the intended format and use Data → Text to Columns → Finish to convert it.^1^

    Do not use IFERROR merely to hide the problem during diagnosis. After correcting the source, it can provide an intentional fallback where a missing result is valid:

    =IFERROR(XLOOKUP(A2,LookupRange,ResultRange),"Not found")
    

    2. Validate the table’s orientation and input-cell mapping

    Since the Data Table has already been created, inspect its configuration rather than recreating it blindly.

    For a one-variable Data Table:

    • Values running down a column require the model’s variable cell in Column input cell; leave Row input cell empty.
    • Values running across a row require the variable cell in Row input cell; leave Column input cell empty.
    • The formula at the top edge of the Data Table must ultimately depend on that same input cell.

    For a two-variable Data Table:

    • The top-left corner contains the output formula.
    • Values across the top row map to Row input cell.
    • Values down the first column map to Column input cell.
    • The output formula must use both input cells.

    Select the complete Data Table range, then open Data → What-If Analysis → Data Table and inspect those references. Excel supports only one or two variables in a Data Table.^2^

    Also confirm that neither the output formula nor any formula feeding it refers to a result cell inside the Data Table. That would create a circular dependency.

    3. Force the Data Table to recalculate

    A correctly configured table can remain stale when calculation is set to Manual or excludes Data Tables.

    In Excel for Windows:

    1. Open File → Options → Formulas.
    2. Under Workbook Calculation, select Automatic.
    3. Open Formulas → Calculate Now.

    If the workbook intentionally uses Automatic Except for Data Tables, select the Data Table formulas and press F9 to calculate them manually. Calculation settings in desktop Excel affect all open workbooks.^3^

    If the source formula calculates normally for every test value, the input references match the table orientation, and recalculation still produces #N/A, the next details needed are:

    • The formula in the Data Table’s top-left/header cell.
    • Whether it is a one- or two-variable table.
    • The selected Row and Column input-cell references.
    • One input value that reproduces the error.

    References

    1. How to correct a #N/A error | Microsoft Support
    2. Calculate multiple results by using a data table
    3. Change formula recalculation, iteration, or precision in Excel
    AI-generated content may be incorrect. Read our transparency notes for more information.

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.