+ Reply to Thread
Results 1 to 16 of 16

Quantity Discount

  1. #1
    Registered User
    Join Date
    09-08-2011
    Location
    Salt Lake City, Utah
    MS-Off Ver
    Excel 2007
    Posts
    5

    Cool 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?
    Attached Files Attached Files
    Last edited by wamba; 09-12-2011 at 04:22 PM. Reason: Solved

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: Quantity Discount

    In your example

    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
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  3. #3
    Registered User
    Join Date
    09-08-2011
    Location
    Salt Lake City, Utah
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Quantity Discount

    Quote Originally Posted by royUK View Post
    In your example

    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. #4
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    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. #5
    Forum Expert
    Join Date
    03-13-2004
    Location
    Gothenburg/Stockholm, Sweden
    MS-Off Ver
    Excel 2003, Excel 2007 & reluctant Excel 2010 user
    Posts
    4,513

    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. #6
    Registered User
    Join Date
    09-02-2011
    Location
    Sheffield, England
    MS-Off Ver
    Excel 2010
    Posts
    10

    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. #7
    Registered User
    Join Date
    09-08-2011
    Location
    Salt Lake City, Utah
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Quantity Discount

    Quote Originally Posted by Alf View Post
    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
    Quote Originally Posted by Mav3r1ck95 View Post
    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. #8
    Registered User
    Join Date
    09-08-2011
    Location
    Salt Lake City, Utah
    MS-Off Ver
    Excel 2007
    Posts
    5

    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. #9
    Forum Expert
    Join Date
    03-13-2004
    Location
    Gothenburg/Stockholm, Sweden
    MS-Off Ver
    Excel 2003, Excel 2007 & reluctant Excel 2010 user
    Posts
    4,513

    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. #10
    Forum Contributor
    Join Date
    09-22-2009
    Location
    Henderson, KY
    MS-Off Ver
    Excel 2007
    Posts
    152

    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
    Last edited by Befuddled; 09-23-2011 at 03:08 AM.

  11. #11
    Forum Contributor
    Join Date
    09-22-2009
    Location
    Henderson, KY
    MS-Off Ver
    Excel 2007
    Posts
    152

    Re: Quantity Discount

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

  12. #12
    Registered User
    Join Date
    09-08-2011
    Location
    Salt Lake City, Utah
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Quantity Discount

    Quote Originally Posted by Alf View Post
    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.
    Attached Files Attached Files

  13. #13
    Forum Expert
    Join Date
    03-13-2004
    Location
    Gothenburg/Stockholm, Sweden
    MS-Off Ver
    Excel 2003, Excel 2007 & reluctant Excel 2010 user
    Posts
    4,513

    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. #14
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    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)
    Last edited by DonkeyOte; 09-24-2011 at 02:33 AM. Reason: added note re: separation

  15. #15
    Forum Expert
    Join Date
    03-13-2004
    Location
    Gothenburg/Stockholm, Sweden
    MS-Off Ver
    Excel 2003, Excel 2007 & reluctant Excel 2010 user
    Posts
    4,513

    Re: Quantity Discount

    Thanks DonkyOte for a most comprehensive explanation!

    Alf

  16. #16
    Registered User
    Join Date
    05-18-2019
    Location
    Luxembourg
    MS-Off Ver
    10
    Posts
    1

    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?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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