Function in row or column for 2 variable data tables

Question

Hello,

I have a book that uses an example for a 2 variable data table. It is a great idea but it does not seem to work. I am trying to verify if I am missing something or it was ill conceived. The title is a drop down box that changes the sell price and avg. Copies/year. The data table columns use sell price + and - to generate values and the rows use avg copies * less and more to create values. The idea is that the data table once generated would be dynamic because you could change the book title and keep seeing the results change. However, it does not calculate correctly without hard coded values. Is there a way to make this work? Any thoughts or ideas are appreciated. Thanks

Title	BookTitle0563
Sell Price	=VLOOKUP(B16,Top_Titles,4)
Avg. Copies/Year	=VLOOKUP(B16,Top_Titles,3)
	=B18*B17	=B17-3	=B17-2	=B17-1	=B17+1	=B17+2	=B17+3
80% of Average	=B18*0.8	=TABLE(B17,B18)	=TABLE(B17,B18)	=TABLE(B17,B18)	=TABLE(B17,B18)	=TABLE(B17,B18)	=TABLE(B17,B18)
90% of Average	=B18*0.9	=TABLE(B17,B18)	=TABLE(B17,B18)	=TABLE(B17,B18)	=TABLE(B17,B18)	=TABLE(B17,B18)	=TABLE(B17,B18)
110% of Average	=B18*1.1	=TABLE(B17,B18)	=TABLE(B17,B18)	=TABLE(B17,B18)	=TABLE(B17,B18)	=TABLE(B17,B18)	=TABLE(B17,B18)
120% of Average	=B18*1.2	=TABLE(B17,B18)	=TABLE(B17,B18)	=TABLE(B17,B18)	=TABLE(B17,B18)	=TABLE(B17,B18)	=TABLE(B17,B18)

Answer 1

You are welcome.

Answer 2

Thanks Ashish,

That was my thought but I wanted to make sure I verified before I contacted the book publisher to tell them the exercise was wrong. To bad, because it would be nice to be able to have a dynamic data table changing based on a drop down list. Thanks for your time and feedback.

Answer 3

Hi,

Id don't think the Data Table will work here because in a Data > Table, each of the numbers in the row and column labels i.e. C20:H20 and B21:B24 would be fed into cells B17 and B18 respectively.  As that happens, a circular reference would arise and Excel would attempt to feed that number back into C20:H20.

Answer 4

Bernie,

I do appreciate you taking the time to reply. I am aware that this could be done through a simple formula with mixed references, and that is probably how I would do it. As I stated though, this comes from an exercise in a teaching book showing an example of creating a 2 Variable data Table that uses formulas in the Rows and Columns of the data table which does not generate accurate results. I am trying to verify whether it is even possible to do this. The cells are off because I did not copy the whole worksheet.

Cell to right of Title is B16 which is a Data Validation Drop Down List changing the Book Title

Cell to right of Sell Price is B17 generated by vlookup of Title Above

Cell to right of Avg Copies is B18 generated by vlookup of Title above

The Data Table is built in B20:H24

This works fine with hard coded values in C20:H20 and B21:B24 However at that point it's not Dynamic and wouldn't change based on the drop down list changing. 

It's a very nice setup if it can work. Thanks

Answer 5

Your first cell (appears to be C20) with

=TABLE(B17,B18)

should be

=$B20*C$19

Copied down and across.

If it is not C20, then use $Column to left /Row  * Column / $ row above:

So, if it is D21, use

=$C21*D$20