Inconsistent Data-table in Excel

Question

My two-dimensional data-table has eight rows and five columns.  I have used the goal-seeking tool to alter a variable in my spreadsheet (that is not one of the two variables specified in my data-table) in order to arrive at a value for a particular item which is in the main body of my spreadsheet.   I then want to see the effects of a range of combinations of two other variables on that particular item.  Hence my data-table.

The standing values in the main body of the spreadsheet for each of the two variables are the same as one member of the respective set of eight or five values for that variable.  However, in the cell in the data-table which corresponds to those particular values, the resultant value for the item is different from that in the top left-hand corner of the data-table and in the main body of the spreadsheet.  I have done a forced "Recalculate", just to make sure.  How can this be?  Can anyone explain?  What can I do to correct the problem which seems to undermine entirely the utility of a data-table.

Answer 1

Although earlier you reported "No circular references," I think that's the problem.

I noted that, for many of your four tables, the five input values across the top of your tables and the eight input values down the left side of your tables are values that are the result of formulas.

So I did a copy and paste special values for each of those eight ranges (row cells and column cells for each of the four tables).

Then, I observe, for each table, the result shown in the body of the table for the "base case" agrees with base case for the top left corner formula.

Answer 2

Hi,

Upload the anonymized version to Google Drive/One Drive and share the download link here.

Answer 3

I cannot replicate your problem.

(A) Choose File > Options > Formulas, and verify the two Calculation options:

Workbook Calculation: Automatic

Enable iterative calculation: unchecked

(B) Select a cell in the body of the data table. The formula bar should show something like

{=TABLE(B2,B4)}

Verify that the first argument (my B2) is the input cell of your model for the row values of the table and the second argument (my B4) is the input cell of your model for the column values of the table.

(C) Are any macros involved in the computations?

(D) Are there any circular references in your model?

Answer 4

Chuanjie, Thank you very much for offering to help.  

I don't know how to attach something as a link, at least not without granting external access to our files, which we would never do.  I could send an anonymised version of the spreadsheet as an attachment to an email if you could let me have an address, or upload it somewhere specified by you.  Or you could explain how to give you access in the way you suggested.  Let me know which would suit you best

CumberlandNick

Answer 5

There are quite a number of reasons why this appears to be the case. The most common one would be that the "Row Input Cell" and "Column Input Cell" is not linked to computation of the results of the rest of the Excel file. If that is not the case, maybe you could post your spreadsheet as a link and we could take a look.