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

Open in new window

And then you can simply use this UDF on the sheet like...
=getTotalPrice(C9)

Open in new window

incremental-example_UDF.xlsm
0
 
JayBMAuthor 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.

All Courses

From novice to tech pro — start learning today.