Table "What If" Set Up If I Change One Or Two Variables

  • Thread starter Thread starter John Johnston
  • Start date Start date
J

John Johnston

I saw someone do this a couple of weeks ago, so maybe
someone can tell me how it's done.
I have the following information:
cars trucks vans average %
4 3 3
4% 3% 3% 3.333%

I want to have Excel create a table that will allow me to
change the number of cars ordered and generate the
adjusted average:
cars average %
4 3.333%
5 3.6666%
6 4%
7 4.333%

Also, what do I do to change two variables. Like this:
cars
3 4 5
vans 2 2.667% 3% 3.333%
3 3% 3.333% 3.667%
4 3.333% 3.667% 4%
 
Assuming you have in A1:D2

cars...trucks...vans...AvUnits
4..... .3.......3... 3.3

Put in D2: =AVERAGE(A2:C2)

the above formula will calculate
the average units per vehicle type (AvUnits)

----------------------------------------------------
A. Setting-up a 1-variable data-table for "cars"
-----------------------------------------------------
Put:

In F3:F6 : 4,5,6,7
In G2: =D2

Select F2:G6

Click Data > Table
Leave row input cell blank
Put in column input cell: $A$2 (ie the cell for "cars")
Click OK

Mask G2 by formatting the font color white, ie = backgrd color
Use a cell *outside* the data-table (which is in F2:G6)
say, cell G1 to label "cars"

This will give you the adjusted AvUnits
based on the # of cars listed in F3:F6
(assuming trucks and vans #s do not change)

------------------------------------------------------------
B. Setting-up a 2-variable data-table for "cars" & "vans"
-----------------------------------------------------------

Put:

In I3:I5 : 2,3,4 (#s for vans)
In J2:L2 : 3,4,5 (#s for cars)
In I2: =D2

Select I2:L5

Click Data > Table
Put in row input cell: $A$2 (ie the cell for "cars")
Put in column input cell: $C$2 (ie the cell for "vans")
Click OK

Mask I2 by formatting the font color white, ie = backgrd color
Use cells *outside* the data-table (which is in I2:L5)
say, cells J1 and H1 to label "cars" and "vans"

This will give you the adjusted AvUnits
based on the # of vans and cars listed in I3:I5 and J2:L2
(assuming trucks # does not change)

hth
Max
 
Back
Top