help

[valerie]

How do i create a table on a worksheet in excel?

 

[Gord Dibben]

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

 

[valerie]

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

 

[valerie]

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.

 

[Gord Dibben]

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

 

[Max]

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 D27
(D27 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

 

[Shiva]

íÞÝØíÈØ