look up table to caclulate pricing

  • Thread starter Thread starter Mark
  • Start date Start date
M

Mark

We are trying to figure out how to use lookup tables to do a
calculation on pricing versus using lots of IF statements.


There are two components to calculating the cost of an item


We will have a spread sheet that has one sheet with the following
labels
A B C
1 Number of User Product Price
2


Number of users is a field that will be input by the user


Product should come from a drop down menu (based on the selection a
different price look up table should be used)


Price is a calculation based on the number of users


There will be two look up tables


Product A
users: 1 - 10 $ 100 each user
users: 11 - 20 $ 20 each user


so for example if you had 11 users you would pay $1020


How do we get the lookup table to keep this uncomplicated


The second issues is how do we point the cost column to different
lookup tables based on the selection they make in column b (product)


Thanks
Mark
 
Mark,

One approach, if you don't have too many price breaks (as a function of
count of users) is to make a price table with product in the first column,
and have columns for the prices. One column would be the 1-10 users column,
etc.. Now you can use a VLOOKUP like

=VLOOKUP( ProductCell, Table, UserCountCell + x)

x is a constant that gets it to the first price column. E.g.: if the 1-10
price column is the fourth in the table, it'd be 3.
 
Hi Mark

One way to achieve what you want is to structure the second spreadsheet as
follows
i.e. for Product A
...........A......................B........................C
1......Num Users........User Cost.........Fixed Cost
2.........1........................100...................0
3.........11.......................20...................=(A3-1)*B2+C2

then select from A1:C3 and create a range name called (insert / name /
define)
ProductA

- this can then be done for each product, set each table up the same way,
and create a range name using the exact product names without spaces

now on you first table
set up the drop down list using data / validation - to display each of the
products in turn
and then in C2 type the following formula
=VLOOKUP(A2,INDIRECT(SUBSTITUTE(B2,"
","")),2)*A2+VLOOKUP(A2,INDIRECT(SUBSTITUTE(B2," ","")),3)

Hope this helps
Cheers
JulieD
 
Back
Top