Rating discrete chunks of a total amount

mzza

mzza

Board Regular
Joined
Nov 8, 2006
Messages
55
Hi,

I am trying to find a formula to handle the following situation.

I am developing pricing for a customer who wants to see incremental benefit to buying more units. Circumstances dictate that this economy of scale must be implemented in a 'stepped' manner, i.e.:

For total number of units up to 1,000, cost is $0.45 per unit.
For total number of units from 1,001 to 5,000, cost is $0.45 per unit for the first 1,000 units and $0.43 per unit thereafter.
For total number of units from 5,001 to 10,000, cost is $0.45 per unit for the first 1,000 units; $0.43 per unit for the next 5,000 units; then $0.40 per unit for each unit thereafter.
... and so on.

Is there a formula I can use to calculate the total cost for a given number of units? I have been playing with nested Ifs to break the total number down into discrete chunks, but it's getting unwieldy and I would like for there to be no theoretical upper limit to the number of units.

TIA

mzza
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
The big question is, how many different "steps" might you have? You can only nest up to 7 IF statements, so if you have more than 7 steps, then you may need to use alternative methods like a lookup table or a User Defined Function.
 
Upvote 0
Hi Joe,

Yep, that's precisely my problem. There are more than 7 seperate pricepoints, so a nested IF won't do it. I have never created a user-defined function, but I'm familiar with V & H Lookups, if that's what you're referring to? What do you recommend?
 
Upvote 0
It actually looks like we may not need VLOOKUPS or VBA. Here is a formula for the example you provided (assuming an entry in cell A6):

=MIN(A6,1000)*0.45
+MIN(4000,MAX(0,A6-1000))*0.43
+MAX(0,A6-5000)*0.4

You should be able to follow the same logic out for more conditions. Note that the first piece will always use just the MIN and the last will always use just the MAX and all the ones in between use both (to capture the upper and lowers limits of that range).
 
Upvote 0
This works perfectly! Thank you so much for the helpful response.

M
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,216,144
Messages
6,129,120
Members
449,488
Latest member
qh017

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top