# Quantity Discount

1. ## Quantity Discount

I offer a price break after every 200 units a customer buys. So the first 200 units are \$1,500 each, the next 200 units are \$1,400 each, and so on.

In a given year, a customer may buy 300 units, so the first 75 may be at the price they were paying the year before, then they reach a price break, get 200 more at a lower price, and then the last 25 at an even lower price.

I know there must be a more elegant way to calculate the total sales for a year than the way I have done it in the attached workbook. The only thing I could think to do is create a table with 1000 rows in it, put the price for every individual unit in the table, and add them up with a SUM(INDEX) formula.

Can anyone help me calculate the totals in cells E3:J3 without creating a 1,000 row table?  Register To Reply

2. ## Re: Quantity Discount

place this formula in F6, the value is entered in F5

=VLOOKUP(E6,A5:B9,2,TRUE)

Because the VLOOKUP's Range_Lookup is set to true it will not find an exact match, but the closest  Register To Reply

3. ## Re: Quantity Discount Originally Posted by royUK place this formula in F6, the value is entered in F5

=VLOOKUP(E6,A5:B9,2,TRUE)

Because the VLOOKUP's Range_Lookup is set to true it will not find an exact match, but the closest
Thanks, @RoyUK, but I don't fully understand your answer. What value is entered in cell F5? And what lookup value is supposed to be in cell E6 to make the formula work?

Your suggestion is almost the the same formula I have in cell B13.  Register To Reply

4. ## Re: Quantity Discount

You enter a value of say 251, the relevant discount rate will be picked

You need to apply that to whatever you are doing, not list it for every unit as you seem to have done  Register To Reply

5. ## Re: Quantity Discount

Based on your example you could try a formula like this:

=SUM((E2>{0,200,400,600,800})*(E2-{0,200,400,600,800})*{1500,-100,-100,-100,-100})

where E2 holds the number of units bought.

All credits for this solution go to DonkeyOte and Colin Legg who taught me to how to do multi-tier calculations.

Alf  Register To Reply

6. ## Re: Quantity Discount

To make Alf's answer work with what's in the example spreadsheet, in cell E3 put the formula ``Please Login or Register  to view this content.``
and then copy and paste across, this gives the exact same result as the original.  Register To Reply

7. ## Re: Quantity Discount Originally Posted by Alf Based on your example you could try a formula like this:

=SUM((E2>{0,200,400,600,800})*(E2-{0,200,400,600,800})*{1500,-100,-100,-100,-100})

where E2 holds the number of units bought.

All credits for this solution go to DonkeyOte and Colin Legg who taught me to how to do multi-tier calculations.

Alf Originally Posted by Mav3r1ck95 To make Alf's answer work with what's in the example spreadsheet, in cell E3 put the formula ``Please Login or Register  to view this content.``
and then copy and paste across, this gives the exact same result as the original.
Thank you, @Alf and @Mav3r1ck95. That is the kind of elegant brilliance I knew I could find here. I love it.  Register To Reply

8. ## Re: Quantity Discount

As a follow up to this thread, once I understood the logic used in the answer, I wanted to be able to change the discounts by updating a cell, rather than having the discounts hard coded in the formula.
So I converted it to a SUMPRODUCT. ``Please Login or Register  to view this content.``
I had to make some minor changes. Cells A5:A9 have to be numbered 0,200,400,etc. instead of 1,201,401,etc. And cell B4 has to be blank for the formula to work.

Thanks again for everyone's help.  Register To Reply

9. ## Re: Quantity Discount

Excellent improvement! Gives a formula like this much more flexibility.

Got a link from DonkyOte that may be of interest

http://www.mcgimpsey.com/excel/variablerate.html

Alf  Register To Reply

10. ## Re: Quantity Discount

ummm... I was trying to teach this to myself, and was following along. I have noticed an issue.

Wamba's new formula DOES NOT do the same thing. It only calculates the cost of all items sold, at the rate for the # of items sold, thru the end of that year. in 2017, they would actually owe money back to the customer, while selling 50 more items, because it would pass through the next pricing threshold.

Shouldn't the answer for 2017 be \$1,162,500.00 ? not \$925,000.00  Register To Reply

11. ## Re: Quantity Discount

Here is a corrected example. Using the info gained off of the link Alf posted  Register To Reply

12. ## Re: Quantity Discount Originally Posted by Alf Excellent improvement! Gives a formula like this much more flexibility.

Got a link from DonkyOte that may be of interest

http://www.mcgimpsey.com/excel/variablerate.html

Alf
@Alf, thanks for the link. That's exactly the kind of thing I was looking for when I posted this question.

One thing I don't understand, though. Why do they put two dashes at the beginning of their sumproduct formulas? What does that do? "=SUMPRODUCT( -- (......"

@Befuddled, your example is doing almost the same thing I did in my version. The only difference is that I had the "Diff. Rate" and the "Overall Total" calculated within the sumproduct formula because I didn't want to take up extra cells with them. My version is attached.  Register To Reply

13. ## Re: Quantity Discount

If I understand it properly the "--" is used in order to transform True/False statement to 1 or 0.

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

that has made me confused on a much higher level than before.

Alf  Register To Reply

14. ## Re: Quantity Discount

The link above explains in detail but Alf's assertion above is correct, namely: Double unary is simply a means to coerce Boolean Array

In Native XL (working in the direction of Boolean to Integer) True is 1 and False is 0.

Coercion is achieved via mathematical operation, hence the alternative methods of:

=SUMPRODUCT((Booleans)*(Booleans)) ... multiplication forces coercion

=SUMPRODUCT(--(Booleans),--(Booleans)) ... double unary forces coercion

Some (self included) prefer

=SUMPRODUCT((Booleans)+0,(Booleans)+0)

This is in part because it's more obvious and also because early on in XL2007 there was a bug relating to use of double unary with volatile precedents (since fixed by MS).

The argument for double unary is that it is the most efficient of the coercion options*, however, most would argue that if efficiency were key concern then you should not be using SUMPRODUCT (or Arrays) in the first instance.

The argument for splitting the various arrays in SUMPRODUCT is to avoid potential for erroneous conversion, assume final array contains both text & numbers

=SUMPRODUCT((Booleans)*(Booleans)*finalarray) -> #VALUE!

=SUMPRODUCT(--(Booleans),--(Booleans),finalarray) -> works like SUMIF and non-numeric entries simply ignored - no #VALUE! error

in short, when using * of arrays in SUMPRODUCT it's best to apply onto to Boolean Arrays and separate the non-boolean arrays:

=SUMPRODUCT((Booleans)*(Booleans),finalarray)

edit:
*: single unary more efficient but adds risk given potential for inverted result pending number of arrays (specifically when modified)  Register To Reply

15. ## Re: Quantity Discount

Thanks DonkyOte for a most comprehensive explanation!

Alf  Register To Reply

16. ## Re: Quantity Discount

what happens if the quantity discount is based on a matrix, for example in your file you have the volume on Y axis but you have the another value eg the time on X axis?  Register To Reply