Sensitivity Analysis in Excel & data tables

[jk3nnedy]

Hello,

I need to set up a simple two-variable sensitivity analysis table. I have researched data tables and attempted to set one up but I get an error.

I believe my problem is the structure of the output formula. When I change the two variables, they affect 4 different outputs, which I then SUM. That SUM is what i'd like to build my sensitivity analysis with.

I can reference a single cell and have it change when I change my two input variables but I still get an error when I use a simple reference as the formula in the data table.

Here is an example of the data I am trying to put into a data table:

In A1, we have a reference to where the output is and B1:D1 is a # of units and A2:A4 is a growth rate. The other cells are the output based on the sum from another page.

='DifferentWorksheet'!$T$36	0	50	100
0%	24,000	28,000	31,000
5%	26,000	30,000	32,000
10%	28,000	32,000	34,000

Also, I have researched different add-ins that apparently do sensitivity analysis but my PC is controlled by IT and I cannot install third-part add-ins.

Does anyone have any insight as to why the data table won't work? Am I correct in my thinking why it wont? Or does anyone have any other suggestions to easily build a sensitivity table?

Any help would be greatly appreciated.

[shg]

Have you done 2D data tables before, you know how they work? You appear to have it set up correctly.

How complicated is the workbook? Would I regret asking you to post it?

[jk3nnedy]

I can't post it unfortunately as it is a product for my job. But I could try and explain a little deeper.

The final SUM is a combination of four different product categories that experience a drop in units and a growth rate at a certain point in my model. Prior to them being summed they have different margins. So I just have a final Summary page that adds up all of the products after they have been affected by their own respective profit margins .

[jk3nnedy]

Figured it out with a little experimenting. Instead of the top left corner of the data table being a reference to a SUM, I made it the full SUM formula and it worked!