Need some help with progressive pricing please

[rga88]

Hello,
I am struggling with a progressive pricing problem and hoping you can help.
I have a unit quantity range and a price I need to calculate.
So for instance, in column B6:B10, I have the unit quantities. In column C6:C10, I have the price per unit.

Unit Quantity (column B) Price Per Unit (column C)
1 to 10 50
11 to 19 40
20 to 29 30
30 to 39 20
40 to 49 10

What I want to do is to put the number of units in cell C12 and have the total price display in C13.
I have tried a couple of vlookup formulas but this is my first time doing this and did not have much luck.
If you could point me in the right direction I would really appreciate it. Thank you!

 

[Marcelo Branco]

Welcome to Mr Excel forum

Try something like this

	A​	B​	C​	D​	E​	F​	G​
1​	Lower​	Higher​	Price​	Marginal Price​		Qty​	Total Price​
2​	1​	10​	50​	50​		12​	580​
3​	11​	19​	40​	-10​
4​	20​	29​	30​	-10​
5​	30​	39​	20​	-10​
6​	40​	49​	10​	-10​

Formula in D2 copied down (helper column)
=C2-N(C1)

Quantity in F2

Formula in G2
=SUMPRODUCT(--(F2>=A2:A6),F2+A2-A2:A6,D2:D6)

Hope this helps

M.

 

[TRADER083]

Or try the following function after pasting into a module, one situation isn't covered here is when units exceed 49. Use it like any functions in worksheets (i.e. "=SUM()")

Public Function amount(units As Long)
If units >= 1 And units <= 10 Then
    amount = units * 50
ElseIf units >= 11 And units <= 19 Then
    amount = units * 40
ElseIf units >= 20 And units <= 29 Then
    amount = units * 30
ElseIf units >= 30 And units <= 39 Then
    amount = units * 20
ElseIf units >= 40 And units <= 49 Then
    amount = units * 10
End If
End Function

 

[JackDanIce]

If data is in range C6:D10, unit value in C12, try this in C13:

=IF(C12=MEDIAN(MIN($B$6:$B$10),MAX($C$6:$C$10),C12),C12*INDEX($D$6:$D$10,MATCH(C12,$B$6:$B$10,1)), "Unit value not found in pricing table")