making a pricing matrix

A

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
 
C

Chip Pearson

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

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,C3:D7,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)
 
G

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
 
A

Aaron

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

Gord Dibben said:
=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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top