1. Hi all,

Could anyone suggest a simple 'one cell' formula that could calculate the following stepped pricing problem.

Eg
The quantity purchased falls between two ranges (Lower & Upper ranges), but it is not a straight lookup

Lower Upper Price
1 10000 0.10
10001 20000 0.08
20001 50000 0.07
50001 80000 0.55
80001 100000 0.35
100000+ 0.25

If the buyer purchases 25000 units, the first 10000 are at 0.1, the next 10000 are at 0.08 and then the next 5000 are at 0.07.
The total cost is therefore :- 1000+800+350 = 2150

Thanks

Rob

2. I devised a formula and a set of broken down formulas to show how it works, see attached.

3. See the attached workbook. I used a table with some auxiliary results; someone else will probably post a formula that doesn't need such a table.

[attachment=86402:Cost.xls]

4. Thanks Guys,

I was working on silmilar lines.
I also couldn't do it without adding at least one additional column for the tiered range.

However, I did discover the "Sumproduct" formula, which helps. Still, my formula is so complex, it seems hardly worth trying to get it down to one cell.

thanks again

Rob[attachment=86404:Tiered Pricing.xls]

5. Here's a version that uses only one auxiliary column: the cumulative price for each tier.

[attachment=86405:Cost.xls]

6. That's much more concise !

Thanks again

Rob

7. I've added a single formula to a slightly modified version of JKP's workbook.
FWIW.

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•