Incremental Unit Band Pricing in Excel


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


Who is Participating?
ShumsDistinguished Expert - 2017Commented:
Hi Try below formula in D9:


Check in attached....
Roy CoxGroup Finance ManagerCommented:
You can use VLOOKUP with the last criteria set to TRUE. Change the first column to whole numbers e.g.

Vlookup Closest Match Example
JayBMAuthor 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.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Roy CoxGroup 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
JayBMAuthor Commented:
Exactly that yes. I've found lots of articles but I can't make them work!
Roy CoxGroup Finance ManagerCommented:
Subodh Tiwari (Neeraj)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
            TotalPrice = TotalPrice + Units * Dict(it)
            Units = 0
        End If
    Next it
getTotalPrice = TotalPrice
End Function

Open in new window

And then you can simply use this UDF on the sheet like...

Open in new window

JayBMAuthor Commented:
The VBA is nice as well, but I'd rather hard code it for this usage.
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.

All Courses

From novice to tech pro — start learning today.