help

  • Thread starter Thread starter valerie
  • Start date Start date
Valerie

More detail on your needs please.

What sort of table?

What data?

Layout of data?

In short........what do you want to do with your table?

Gord Dibben XL2002
 
Country Item Manufacturer’sCost Agent’s cost
Singapore Lipstick $6.25 This are to be taken fm
calculations;(160%
of manufacturer’s cost)
Singapore Cleanser $9.90
Singapore Toner $7.50
Malaysia Lipstick $9.85
Malaysia Cleanser $15.90
Malaysia Toner $11.90
 
I need to create a two-variable data table;
Country Item Manufacturer’sCost
Singapore Lipstick $6.25
Singapore Cleanser $9.90
Singapore Toner $7.50
Malaysia Lipstick $9.85
Malaysia Cleanser $15.90
Malaysia Toner $11.90
For the agent's cost, this are to be taken fm calculations in formulas,
which is 160% of the manufacturer's cost.
So, how should i go about doing it?
Thanks.
 
Agent's cost would be 1.6 times Manufacturer's cost.

Assume Mfr's cost in Column C and Agent's cost in Column D

In D2 enter =C2*1.6

Drag/copy down Column D until you run out of data in Column C.

Gord Dibben XL2002
 
Here's some steps to set-up a 1*-variable data table
for the case stated in your post:

*I see only 1 what-if variable in the case described

Assuming your data as posted is in A1:C7
i.e. col A = Country, col B = Item, col C = Manufacturer's Cost

Put in A9: Agent_Percent_of_Manu_Cost (just a label)
Put in B9: 160% (this will be the what-if-variable)

Put in D1: Agent's Cost (just a label)
Put in D2: =C2*$B$9
Copy down D2:D7
(D2:D7 will be the what-if-dependencies,
viz the 6 country_items)

Setting up the Data-Table
-------------------------------
Enter in say A12:A20

Agent_Percent_of_Manu_Cost
130%
140%
150%
160%
170%
180%
190%
200%

Construct the col labels in B11:G11:

Put in B11
: =INDIRECT("A"&COLUMN())&"_"&INDIRECT("B"&COLUMN())

Copy across B11:G11

In B11:G11 will be the col labels,
drawing from the 6 country_items in A2:B7

Put in B12: =INDIRECT("D"&COLUMN())

Copy across B12:G12

The above gives the equivalent of putting
in B12: =D2, in C12: =D3, in D12: =D4, etc

Select A12:G20

Click Data>Table

In the Table dialog box:
a. Leave row input cell empty
b. For column input cell, enter: $B$9 (or just click on B9)
c. Click OK

Mask B12:G12 by formatting the font in white,
ie in the same colour as background

Format B13:G20 as Currency

The data table will calculate the sensitivities of all the 6 dependencies,
ie the Agent's Cost for each of the country_item,
to the single what-if variable Agent_Percent_of_Manu_Cost.

You can also plug in other values under
the Agent_Percent_of_Manu_Cost col in the data table
(instead of 130%, 140%... 200%)
which will then be recalculated accordingly in the data table.

Hope the above helps.

Max
 
Back
Top