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

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Lower​

[/td][td]

Higher​

[/td][td]

Price​

[/td][td]

Marginal Price​

[/td][td][/td][td]

Qty​

[/td][td]

Total Price​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

1​

[/td][td]

10​

[/td][td]

50​

[/td][td]

50​

[/td][td][/td][td]

12​

[/td][td]

580​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

11​

[/td][td]

19​

[/td][td]

40​

[/td][td]

-10​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

20​

[/td][td]

29​

[/td][td]

30​

[/td][td]

-10​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

30​

[/td][td]

39​

[/td][td]

20​

[/td][td]

-10​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

40​

[/td][td]

49​

[/td][td]

10​

[/td][td]

-10​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]

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")