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?

[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.

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
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
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

JayBMAuthor Commented:
Exactly that yes. I've found lots of articles but I can't make them work!
0
Roy CoxGroup Finance ManagerCommented:
0
ShumsExcel & 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.

Start your 7-day free trial
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
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.