making a pricing matrix

[Aaron]

To all,

I'm trying to make a formula that will compute a selling price based on my
matrix to work on a whole list of costs. THe problem is that the multiplier
changes depending on the cost of the item. Here is my multiplier list:

$.01-$50 *1.35
50.01-75 *1.3
75.01-150 *1.25
150.01-250 *1.2
250.01-99999 *1.15

I've made a formula before using IF function that I can just enter the cost
of a piece and it will give me a selling price for it, but I'm having a hard
time making the formula replicate itself for a report where I'm going to have
a whole list of different costs and need a whole list next to it of different
selling prices.


Any help would be appreciated.

Thanks,
Aaron

 

[Chip Pearson]

You can use the VLOOKUP function to do this. For example, enter the
following values in C37

0 1.35
50.01 1.30
75.01 1.25
150.01 1.20
250.01 1.15

Then put the price in G9 and use the following formula:

=VLOOKUP(G9,C37,2,TRUE)

This will return the amount (1.35, 1.30, etc) that corresponds to the
value in G9.

You could also use

=1.35-((MATCH(G9,C3:C7,1)-1)*0.05)

to get the value corresponding to the value in G9. In this case, you
don't need the numbers in the second column, only the first column.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Gord Dibben]

=LOOKUP(A1,{0,50.01,75.01,150.01,250.01},{1.35,1.3,1.25,1.2,1.15})*cellref

Where A1 holds the cost and cellref is the value to be multiplied.


Gord Dibben MS Excel MVP

 

[Aaron]

Great!! Thanks!! Works just how I imagined.

=LOOKUP(A1,{0,50.01,75.01,150.01,250.01},{1.35,1.3,1.25,1.2,1.15})*cellref

Where A1 holds the cost and cellref is the value to be multiplied.


Gord Dibben MS Excel MVP