# Incremental Unit Band Pricing in Excel

Hi,

I need help with incremental band pricing in excel.

Please see attached. I would like a formula to auto calculate the total value with different unit pricing for each band.

The example I found on here for MJs make no sense to me.incremental-example.xlsx

Thanks!

James
###### Who is Participating?

Distinguished Expert - 2017Commented:
Hi Try below formula in D9:

=IF(AND(C9>=1,C9<=10),(C9*200),IF(AND(C9>=11,C9<=25),(10*200)+((C9-10)*185),IF(AND(C9>=26,C9<=50),(10*200)+(15*185)+((C9-25)*169),IF(C9>=51,(10*200)+(15*185)+(25*169)+((C9-50)*155),""))))

Check in attached....
JayBM_incremental-example.xlsx
1

Group Finance ManagerCommented:
You can use VLOOKUP with the last criteria set to TRUE. Change the first column to whole numbers e.g.
10
25
50
100

Vlookup Closest Match Example
0

Author Commented:
That would only add up a single band. So all the units would take on the price unit for that band. I need it incremental so it takes the first band unit pricing then adds it to the second band unit pricing and so on.

I already tried it with: =VLOOKUP(G3,O2:P6,2,TRUE)*G3 which just sets all the unit pricing for the band the total number is in. I need it incremental.
0

Group Finance ManagerCommented:
That's the usual way I would expect it work. So what you want is like the first 10 @ price 1, the next 15 @ price 2
0

Author Commented:
Exactly that yes. I've found lots of articles but I can't make them work!
0

Group Finance ManagerCommented:
0

Excel & VBA ExpertCommented:
I would prefer doing it with VBA.
You may also tweak the price table if a need arises.

``````Function getTotalPrice(TotalUnits As Long) As Double
Dim Units As Long, TotalPrice As Double, i As Long
Dim x, Dict, it

x = Range("C3:D6").Value
Set Dict = CreateObject("Scripting.Dictionary")

For i = 1 To UBound(x, 1)
Dict.Item(x(i, 1)) = x(i, 2)
Next i

Units = TotalUnits

Do Until Units = 0
For Each it In Dict.keys
If it <= Units Then
TotalPrice = TotalPrice + it * Dict.Item(it)
Units = Units - it
Dict.Remove it
Exit For
Else
TotalPrice = TotalPrice + Units * Dict(it)
Units = 0
End If
Next it
Loop
getTotalPrice = TotalPrice
End Function
``````
And then you can simply use this UDF on the sheet like...
``````=getTotalPrice(C9)
``````
incremental-example_UDF.xlsm
0

Author Commented:
The VBA is nice as well, but I'd rather hard code it for this usage.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.