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

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

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.

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

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

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.

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.

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.

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

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

11. ## Re: Quantity Discount

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

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.

13. ## Re: Quantity Discount

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

See this most comprehensive link about SUMPRODUCT:

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

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

Alf

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)

15. ## Re: Quantity Discount

Thanks DonkyOte for a most comprehensive explanation!

Alf

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?

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1