+ Reply to Thread
Results 1 to 17 of 17

I am finding it difficult to enter a formula to calculate the discount!

  1. #1
    Registered User
    Join Date
    02-19-2014
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    44

    I am finding it difficult to enter a formula to calculate the discount!

    Hi all, I have attached the Excel 2010 Workbook which I am referring to in this thread.

    I am finding it difficult to enter a formula to calculate the discount. The 12% discount is only applicable to 10 OR MORE of the SAME products ordered (for example, if 10x 17" wheels only are ordered and 7x 18" wheels only are ordered, then the discount is only applicable for the 17" wheels. However if 10x 17" wheels only are ordered AND 10x 18" wheels only are ordered, then 12% discount is given EACH to BOTH of the products).

    I want to know how to calculate the total discount (in cell F25). I have tried using the IF function and the LOOKUP function but both times it has become complicated and hard to get my head around.



    Thanks in advance!!!!

    Moderator's note: I have deleted the file, I just noticed it contains sensitive info (email and pp number)
    Last edited by FDibbins; 02-19-2014 at 06:16 PM.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: I am finding it difficult to enter a formula to calculate the discount!

    Hi and welcome to the forum

    All sorts of ways to do this, here is 1...
    =D8*E8*IF(E8>=10,0.88,1)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    02-19-2014
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    44

    Re: I am finding it difficult to enter a formula to calculate the discount!

    Quote Originally Posted by FDibbins View Post
    Hi and welcome to the forum

    All sorts of ways to do this, here is 1...
    =D8*E8*IF(E8>=10,0.88,1)
    thank you!

    I tried that but a number still shows up if I enter 5 as the quantity?

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: I am finding it difficult to enter a formula to calculate the discount!

    Well isnt that supposed to happen? Unit Price*Qty = Total...the total would be shown no matter what, it would be adjusted down if 10 or more are sold?

    15" Wheels Only...£120...5...£600.00
    15" Wheels Only...£120...10...£1200.00
    but with the discount applied...1056

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: I am finding it difficult to enter a formula to calculate the discount!

    C
    D
    E
    F
    7
    DESCRIPTION UNIT PRICE QUANTITY TOTAL (EX VAT)
    8
    15" Wheels Only £120
    10
    £1,056.00
    9
    16" Wheels Only £160
    10
    £1,600.00
    10
    17" Wheels Only £200
    £0.00
    11
    18" Wheels Only £245
    £0.00
    12
    19" Wheels Only £330
    £0.00
    13
    15" Wheels with Budget Tyres £165
    £0.00
    14
    16" Wheels with Budget Tyres £215
    £0.00
    15
    17" Wheels with Budget Tyres £235
    £0.00
    16
    18" Wheels with Budget Tyres £365
    £0.00
    17
    19" Wheels with Budget Tyres £515
    £0.00
    18
    15" Wheels with Premium Tyres £185
    £0.00
    19
    16" Wheels with Premium Tyres £235
    £0.00
    20
    17" Wheels with Premium Tyres £270
    £0.00
    21
    18" Wheels with Premium Tyres £420
    £0.00
    22
    19" Wheels with Premium Tyres £570
    £0.00
    23
    SUBTOTAL (EX VAT)
    £2,656.00
    24
    VAT
    £531.20
    25
    DISCOUNT
    £144.00
    26
    DELIVERY CHARGE (EX VAT)
    £40.00
    27
    TOTAL (EX VAT)
    £2,552.00


    F8=D8*E8*IF(E8>=10,0.88,1) copied down
    F25=SUMPRODUCT(D8:D22,E8:E22)-SUM(F8:F22)
    F26=SUM(E8:E22,20)

  6. #6
    Registered User
    Join Date
    02-19-2014
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    44

    Re: I am finding it difficult to enter a formula to calculate the discount!

    Quote Originally Posted by FDibbins View Post
    Well isnt that supposed to happen? Unit Price*Qty = Total...the total would be shown no matter what, it would be adjusted down if 10 or more are sold?

    15" Wheels Only...£120...5...£600.00
    15" Wheels Only...£120...10...£1200.00
    but with the discount applied...1056
    oh yeah of course:P

    but how would I write one formula for the total discount? so instead of the formula being only for total of 15" wheels, how would I do it for all of the parts in one formula?

  7. #7
    Registered User
    Join Date
    02-19-2014
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    44

    Re: I am finding it difficult to enter a formula to calculate the discount!

    Quote Originally Posted by FDibbins View Post
    C
    D
    E
    F
    7
    DESCRIPTION UNIT PRICE QUANTITY TOTAL (EX VAT)
    8
    15" Wheels Only £120
    10
    £1,056.00
    9
    16" Wheels Only £160
    10
    £1,600.00
    10
    17" Wheels Only £200
    £0.00
    11
    18" Wheels Only £245
    £0.00
    12
    19" Wheels Only £330
    £0.00
    13
    15" Wheels with Budget Tyres £165
    £0.00
    14
    16" Wheels with Budget Tyres £215
    £0.00
    15
    17" Wheels with Budget Tyres £235
    £0.00
    16
    18" Wheels with Budget Tyres £365
    £0.00
    17
    19" Wheels with Budget Tyres £515
    £0.00
    18
    15" Wheels with Premium Tyres £185
    £0.00
    19
    16" Wheels with Premium Tyres £235
    £0.00
    20
    17" Wheels with Premium Tyres £270
    £0.00
    21
    18" Wheels with Premium Tyres £420
    £0.00
    22
    19" Wheels with Premium Tyres £570
    £0.00
    23
    SUBTOTAL (EX VAT)
    £2,656.00
    24
    VAT
    £531.20
    25
    DISCOUNT
    £144.00
    26
    DELIVERY CHARGE (EX VAT)
    £40.00
    27
    TOTAL (EX VAT)
    £2,552.00


    F8=D8*E8*IF(E8>=10,0.88,1) copied down
    F25=SUMPRODUCT(D8:D22,E8:E22)-SUM(F8:F22)
    F26=SUM(E8:E22,20)


    OH thank you VERY MUCH!!!!

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: I am finding it difficult to enter a formula to calculate the discount!

    you'r welcome. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below my 1st post to you)

    Also, please don't quote whole posts -- it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding.

  9. #9
    Registered User
    Join Date
    02-19-2014
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    44

    Re: I am finding it difficult to enter a formula to calculate the discount!

    Ok I will FDibbins
    So you mean like this reply?
    And last question, how would I make the delivery charge = "£0.00" if there are no quantities selected?

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: I am finding it difficult to enter a formula to calculate the discount!

    If you meant F26, try this...
    =IF(F23=0,0,SUM(E8:E22,20))

  11. #11
    Registered User
    Join Date
    02-19-2014
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    44

    Re: I am finding it difficult to enter a formula to calculate the discount!

    Yes that's worked!
    Thank you very much for your help!!

  12. #12
    Registered User
    Join Date
    02-19-2014
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    44

    Re: I am finding it difficult to enter a formula to calculate the discount!

    Oh ive got a last question!
    I want to put the discount result as a minus sign (if there is a discount), how would I do that?

  13. #13
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: I am finding it difficult to enter a formula to calculate the discount!

    Swap the signs in F25...
    =-SUMPRODUCT(D8:D22,E8:E22)+SUM(F8:F22)
    change F27 to...
    =F23+F26+F25

  14. #14
    Registered User
    Join Date
    02-19-2014
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    44

    Re: I am finding it difficult to enter a formula to calculate the discount!

    thank you it worked

  15. #15
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: I am finding it difficult to enter a formula to calculate the discount!

    Quote Originally Posted by rmp123 View Post
    Hi, about the same workbook.. how would I make the delivery charge ONLY APPLICABLE if the subtotal is less than 18000? i.e the delivery charge will be zero even if +10 products are ordered IF the subtotal is MORE THAN 180000?
    Change it to...
    =IF(OR(F23=0,F23>18000),0,SUM(E8:E22,20))

  16. #16
    Registered User
    Join Date
    02-19-2014
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    44

    Re: I am finding it difficult to enter a formula to calculate the discount!

    thank you!

  17. #17
    Registered User
    Join Date
    02-19-2014
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    44

    Re: I am finding it difficult to enter a formula to calculate the discount!

    How would I calculate the delivery charge of a cell E8? the delivery charge is £20 + EACH set of wheels ordered i.e. dependant on the quantity...?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 2
    Last Post: 12-20-2013, 12:12 AM
  2. Percentage discount formula for a grid of figures and a different discount figure
    By SkydiveAnnie in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-31-2013, 11:09 AM
  3. [SOLVED] Finding % discount via VLOOKUP
    By craigy09 in forum Excel General
    Replies: 8
    Last Post: 06-21-2012, 06:37 AM
  4. Charts - why am I finding this so difficult?
    By conks in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 05-20-2009, 09:14 AM
  5. Finding Sets - Difficult one !
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-21-2006, 05:15 AM

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