+ Reply to Thread
Results 1 to 10 of 10

apply discount based on number of guests

  1. #1
    Registered User
    Join Date
    08-01-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003
    Posts
    5

    apply discount based on number of guests

    I am trying to make a spreadsheet to calculate prices for a customer, the problem I am having is applying discounts based on the amount that they purchase. For example;

    A1 is # of customers A2 is price per unit
    B2 is sub total

    This is easy enough, my problem is I want to make a rule for B2 that states "If the # in A1 is greater than 20 than take off 1xA2"

    How is this done?
    Thanks for the help folks!!!

  2. #2
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: apply discount based on number of guests

    dacraig,

    Welcome to the forum!
    Something like this?
    Please Login or Register  to view this content.
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    08-01-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: apply discount based on number of guests

    Ok, thank you for your help...I guess because there's more lines it complicates it further, so here is what I have,

    A1 is # of customers A2 is price per unit A3 is Subtotal for line A
    B2 is # of customers B2 is (different) PPU B3 is Subtotal for line B
    C3 is =A3+B3

    Now what I want, is for C3 to reflect, that if (A1+B1)>20 than C3 should minus 1*A2, but this should also include the total of A3+B3.

    Sorry if that's worded confusingly...i'm trying my best here....

  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,945

    Re: apply discount based on number of guests

    lol yes it is a little confusing, but try this in C3....

    =if(A1+B1>20,(A1+B1)-(1*A2)+A3+B3,A1+B1)

    if this doesnt work for you, i would suggest uploading a sample file for us to work with?
    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

  5. #5
    Registered User
    Join Date
    08-01-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: apply discount based on number of guests

    Thank you, That did work, but it only when the values in A1+B1 were over 20, if they were 20 or under it equaled whatever the added # of A1+B1 was.

    I would be happy to provide a sample file. but I am unsure how to do so. If you could explain or provide a link to the explanation

  6. #6
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: apply discount based on number of guests

    To Attach a File:

    1. Click on Go Advanced
    2. In the frame Attach Files you will see the button Manage Attachments
    3. Click the button.
    4. A new window will open titled Manage Attachments - Excel Forum.
    5. Click the Browse... button to locate your file for uploading.
    6. This will open a new window File Upload.
    7. Once you have located the file to upload click the Open button. This window will close.
    8. You are now back in the Manage Attachments - Excel Forum window.
    9. Click the Upload button and wait until the file has uploaded.
    10. Close the window and then click Submit.

  7. #7
    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,945

    Re: apply discount based on number of guests

    ok, then try =if(A1+B1>20,(A1+B1)-(1*A2)+A3+B3,A1+B1+A3+B3) from what you said, it looked like the +A3+B3 only applied to over 20

  8. #8
    Registered User
    Join Date
    08-01-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: apply discount based on number of guests

    That last equation ended up giving me a wrong price when A1+A2 were less than 20.

    I have included the sample on this post.
    Attached Files Attached Files

  9. #9
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: apply discount based on number of guests

    dacraig,

    Based on this post:
    Quote Originally Posted by dacraig View Post
    A1 is # of customers A2 is price per unit A3 is Subtotal for line A
    B2 is # of customers B2 is (different) PPU B3 is Subtotal for line B
    C3 is =A3+B3

    Now what I want, is for C3 to reflect, that if (A1+B1)>20 than C3 should minus 1*A2, but this should also include the total of A3+B3.
    The formula you're looking for is:
    Please Login or Register  to view this content.


    But based on your attachment:
    Quote Originally Posted by dacraig View Post
    I have included the sample on this post.
    You have 3 lines instead of 2, and they are not in the cells you specified. Using the same logic however, you can use this formula in C7:
    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    08-01-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: apply discount based on number of guests

    THANK YOU!!! great job!

+ 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