whatif data table shows some right values and other wrong values

[John Macdonald]

I'm tearing my hair out over a data table problem. I'm working out the profitability of a business proposal. In a simplified version to illustrate the problem, I have created a spreadsheet with only two input parameters that can be varied: the number of clients and the daily fee. The Income is a simple function of these two numbers. The Expenditure is a single fixed cost plus a staff cost that depends in some non-formulaic way only on the number of clients. In the simplified sheet, I have created a table showing the staff cost for a range of client numbers -- in the simplified sheet I have not calculated these costs, but just typed in the numbers. To keep this very simple, I have created only two staff costs: one for client number less than 13 and another for client number of 13 or more. (In my real problem, the relationship is more complex, but this simplified version illustrates the same problem.) I want to create a whatif data table for a range of client numbers and a range of fees. The calculation of the income is trivial. The calculation of the expenditure uses either lookup() of vlookup() on the staff-stable or a formula containing multiple nested if functions (I have tried all three and all give the correct answer for the expenditure.) The resulting whatif table signals no error and shows the correct profit for 13 or more clients. However, for less than 13 clients it fails to resister the reduction in staff cost, but carries on using the higher value, so that the profit is spectacularly wrong in these cases. The result is independent of which of the three methods I use to calculate expenditure. What have I done wrong? How can I fix this? I can send a link to the simple spreadsheet if you like. Thanks for any help.

[#2]

Hey John-

Can you supply a small portion of the workbook you're working on in order to give the forum members a better representation of the data and the formula layout?  It's always nice to have a before.... i.e. what the formulas are doing now that is incorrect and an after....what they are supposed to.  This will help others to test their solutions and see if they will give you the correct end result.

I know you mentioned that your model may be a little more complex then you mentioned.  So maybe just a dumbed down version will suffice....

[#3]

Here's a link to a much simplified version of the situation, but it still shows the problem with the data table ... https://readingmencap-my.sharepoint.com/:x:/g/personal/john_readingmencap_org_uk/ERCw4olilitNmpIRlER...

[#4]

This might be a better link: https://www.dropbox.com/s/4d7dq5ftorw9y5x/DemoOfWhatIfFail.xlsx?dl=0

[#5 · Accepted solution]

John,

you have to swap the cell references in the data table.

[#6]

Wow! It was that simple ... thanks, Detlef, that is brilliant! All working now.

In my defence, I interpreted the prompt "Row input cell" as referring to the cell that was constant for each row of the data table and so forming a row label, and the "Column input cell" as referring to the cell that was constant for each column of the data table and so forming a column label. However, Excel wants it the other way around: the "Row input cell" refers to the cell that is varied to produce a row of column headings in the table, and "Column input cell" refers to the cell that is varied to produce a column of row labels in the data table. I reckon this is a bit ambiguous, but, hey, the problem is solved. I should have tried doing that. 

Thanks again.

John

====

[#7]

... What confused me was that some of the results in the table were right, and some were wrong, so I dismissed the idea that I'd just made a simple error in setting it up. I guess that must have been an artefact of my particular data.