If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
making a pricing matrix
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 |
#2
|
|||
|
|||
making a pricing matrix
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) On Tue, 19 May 2009 13:12:01 -0700, Aaron wrote: 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 |
#3
|
|||
|
|||
making a pricing matrix
=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 On Tue, 19 May 2009 13:12:01 -0700, Aaron wrote: 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 |
#4
|
|||
|
|||
making a pricing matrix
Great!! Thanks!! Works just how I imagined.
"Gord Dibben" wrote: =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 On Tue, 19 May 2009 13:12:01 -0700, Aaron wrote: 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 |
Thread Tools | |
Display Modes | |
|
|