Report, from assumed set of variables.

[alexhett]

I have a cell which is dependent upon 2 other cells. For simplicity's sake let's say:

Z9 = (A1*B2)

I would like to generate a report table of various values that Z9 could be.

<table x:str="" style="border-collapse: collapse; width: 192pt;" width="256" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" span="4" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td colspan="4" class="xl25" style="height: 12.75pt; width: 192pt;" width="256" height="17">Various Values of that Z9 could be.</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td class="xl24">A1=1</td> <td class="xl24">A1=2</td> <td class="xl24">A1=3</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">B2=1</td> <td class="xl24" x:num="">1</td> <td class="xl24" x:num="">2</td> <td class="xl24" x:num="">3</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">B2=2</td> <td class="xl24" x:num="">2</td> <td class="xl24" x:num="">4</td> <td class="xl24" x:num="">6</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">B2=3</td> <td class="xl24" x:num="">3</td> <td class="xl24" x:num="">6</td> <td class="xl24" x:num="">9</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">B2=4</td> <td class="xl24" x:num="">4</td> <td class="xl24" x:num="">8</td> <td class="xl24" x:num="">12</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">B2=5</td> <td class="xl24" x:num="">5</td> <td class="xl24" x:num="">10</td> <td class="xl24" x:num="">15</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">B2=6</td> <td class="xl24" x:num="">6</td> <td class="xl24" x:num="">12</td> <td class="xl24" x:num="">18</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">B2=7</td> <td class="xl24" x:num="">7</td> <td class="xl24" x:num="">14</td> <td class="xl24" x:num="">21</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">B2=8</td> <td class="xl24" x:num="">8</td> <td class="xl24" x:num="">16</td> <td class="xl24" x:num="">24</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">B2=9</td> <td class="xl24" x:num="">9</td> <td class="xl24" x:num="">18</td> <td class="xl24" x:num="">27</td> </tr> </tbody></table>
The closest thing that I know is an if(AND( function, something like:

=if(AND(A1=1,B2=2), Z9, "-")

That would work fine for any given cell, but the problem is that A1 and B2 would be different across this whole table.

I'd like a function that does something like this:

RETURN(Z9) AsIf AND(A1=1, B2=2).

and then continues to do that across the whole table with varying values for A1 and B2.

or

ASSUME(AND(A1=1, B2=2), then return what Z9 would be), then
ASSUME(AND(A1=2, B2=2), then return Z9)
ASSUME(AND(A1=3, B2=2), Z9)
ASSUME(AND(A1=1, B2=3), Z9)
ASSUME(AND(A1=2, B2=3), Z9)
...
and so on.
...

to be even more redundant...

I'd like to return what Z9 WOULD BE, if A1 is equal to 1 and B2 is equal to 2. And then do that across a whole table.

I'm hoping there is a simple way to do this, and that I just don't know the name of the function.

(my example is very simple, and I know if that were the case, I could just make three rows of formulas akin to =CellAlongLeft*$CellAlongTop). But my real spreadsheet is much more complicated and I'm trying to be elegant.

[In reality A1 changes all the values in a table, B2 is a date (aka row number) and Z9 performs a vlookup to find the value on the table corresponding to B2. - All with nested if statements, finance calculations, sums, ranges, names, etc. etc.]

Thank you in advance for your help! I really appreciated it.

 

[Damon Ostrander]

Hi alexhett,

What you describe is exactly what an Excel Data Table does. Look at the Excel help unter the topic "Create a two-variable data table". It is a bit tricky the first time, but once you get it down you'll find it is a very useful capability and I suspect you'll be using it a lot.

Keep Excelling.

Damon

 

[gardnertoo]

Alex,

Without seeing the sheet, I can only guess. However, it seems that the crux of your task is the table, the one that changes depending on the A1 data. An inelegant solution would be to create copies of that table, one copy for each possible A1 value you want to test. Then you could do your "what if" lookups against these "what if" copies of the real table, and leave the real table alone to work on the real data. So the first column of hypotheticals would do the lookup for each hypothetical B2 value using copy #1 of the table, the second column uses copy #2, etc.

Another solution could be executed using VBA. A macro could be written to record the starting values in A1 and B2, then replace those values with the hypothetical values for those two cells and record the resulting Z9 value in the corresponding cell in your hypothetical results table, then finish by restoring the as-found values back to A1 and B2.

 

[alexhett]

Thanks Damon,

Data table was exactly what I needed!

I appreciate it very much!

One Caveat I found though: It seems I can only create a data table on the same sheet that the Row Input Cell and Column Input Cell are in.

Otherwise I got errors.

It would be even more elegant If I could create a data table on a different sheet (then again I'm still using 2003, maybe you can now, or in 2010).

Anyway, Thanks! You solved my problem.