ARRAY formula question

[sschwant]

I have a 3 column table with 27 rows. Col 1 row 1 is hard coded and represents a dollar amount. Remaining rows in column 1 are formula driven based on the first row, e.g., row 1 col 1 = $150, then the rest of the rows in col 1 increase by some increment, e.g., + 25, so row 2 - 27 = $175, $200, 225, etc.

The second column contains an array formula which calculates a rebate figure, based on the number in the first column (as far as I can tell). The ARRAY looks as such: {=TABLE(,K10)}

By the way ... the columns are J, K, and L or 1,2,3, respectively on the worksheet. The array formula above is the same in rows 1-27 in col K.

I'm trying to understand the logic and the math here and can't figure it out since the result of the ARRAY/s in col K are not availble via Formula Auditing as there are no "valid references".

 

[pgc01]

Hi

That is not an array formula, that's a 1 Variable Data Table (from the What-If Analysis).

It's a great tool for ex. for sensitivity analysis.

See, for ex., here:

Calculate multiple results by using a data table - Excel

 

[sschwant]

Hi

That is not an array formula, that's a 1 Variable Data Table (from the What-If Analysis).

It's a great tool for ex. for sensitivity analysis.

See, for ex., here:

Calculate multiple results by using a data table - Excel

Fantastic! Thanks for the insight. I will investigate further . . . however . . . even if it is a data table, shouldn't there be some way to figure out what exactly the formula is ? There doesn't seem to be any way to do that in this case . . . given the formerly shown cell contents w/ {} isn't that a clear indication that an array has been setup? E.g., when I use the Home / Go To / Special / Array menu options for that worksheet, it highlights the entire table J:L 1-27 as an array ... but when I click on any cell in col K and use the formula audit tool / trace precedents ... it shows nothing as other then a result of "no valid references" ...

Thanks again for your insight!

Best,

sschwant

 

[pgc01]

even if it is a data table, shouldn't there be some way to figure out what exactly the formula is ?

The formula right above the second column gives you the logic.

The values in the second column are what the result of the formula would be if K10 was equal to each of the values in the first column.

This is why the Data Table is so great for sensitivity analysis.

Let's see if a small example helps clarifying it:

You work in finance and you are building a model to calculate some future value.
You start by assuming the inflation of 2% that you write in K10.
You write do a bunch of complex formulas and get the end result in J10.

Now that you have your model you can see what would happen to the end result for other values of inflation, for ex. 1.8%,1.9%,2.1%,2.2%, etc., replacing K10 by those values.

The problem is that you get 1 value at a time. If you wanted a list of the end result values for 10 different inflations you'd have to replace K10 10 times and note the end result somewhere.

This is what the Data Table does for you.
It allows you to specify a list of values for the input of your model (in this case K10), that you list in the first column, and applies all the logic of your model giving your the result of the calculations for each value of the input in the second column.

In conclusion:
To build the Data you specify:
- the cell in your model that is your input, K10
- the cell in your model that is your output, J10
- a list of values in the first column that will be the inputs you want to test

You get as result the second column, the result of your model to each value of the input in the first column.

Is this clearer?

 

[sschwant]

PGC,

You are correct. I believe that's exactly what has been built into this pricing model. Thanks for the additional clarification ... yes, much clearer.

Can you offer any additional insight as to why the formula itself appears to be an array? Perhaps that's just how the Data Table function looks? (like someone entered the foruma using Control Shift Enter).

Lastly, this model is full of data tables. Is there any way to establish an audit trail in order to follow the general program flow within a work book, given that the Formula Auditing tools show nothing other then something to the effect of "no valid references".

Thanks again for any insights you can further share

Best rgds,

Steve

 

[pgc01]

Hi

Can you offer any additional insight as to why the formula itself appears to be an array? Perhaps that's just how the Data Table function looks? (like someone entered the foruma using Control Shift Enter).

Yes, it's how the Data Table function looks. It tells you that it is a a data table and gives you the input cells.

Lastly, this model is full of data tables. Is there any way to establish an audit trail in order to follow the general program flow within a work book, given that the Formula Auditing tools show nothing other then something to the effect of "no valid references".

All the cells in each data use the same formula and so you just have to analyse those formulas to get the program flow.