# 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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

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
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.