+ Reply to Thread
Results 1 to 7 of 7

Capped discount formula help

  1. #1
    Registered User
    Join Date
    08-02-2020
    Location
    Wales
    MS-Off Ver
    excel 2016
    Posts
    3

    Capped discount formula help

    Hi,
    would be grateful if anyone could help but I need a formula that does the following:
    Calculates a discount of 50% based on a cap of 10 per person based on the total for Food and Non-Alcoholic Drinks ONLY. I have put some examples in of where the cap will apply and where it won't. If anyone can help, thank you in advance, much appreciated. I think I have attached it, Eat out to help out spreadsheet. Thanks
    Amanda
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    42,129

    Re: Capped discount formula help

    Welcome to the forum.

    Try this:

    =IF(H4>=10,C4*10,I4)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Registered User
    Join Date
    08-02-2020
    Location
    Wales
    MS-Off Ver
    excel 2016
    Posts
    3

    Re: Capped discount formula help

    That's amazing thank you, been pondering this for days. Thanks

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    42,129

    Re: Capped discount formula help

    Pleasure!

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

  5. #5
    Registered User
    Join Date
    08-02-2020
    Location
    Wales
    MS-Off Ver
    excel 2016
    Posts
    3

    Re: Capped discount formula help

    Thanks for the previous formula, just having a problem with column K now, total should read 12 but its trying to calculate to max of 40. Any ideas?
    Attached Files Attached Files

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    42,129

    Re: Capped discount formula help

    Why should it be 12? Explain your logic.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    42,129

    Re: Capped discount formula help

    Maybe this in J4?

    =MIN(C4*10,I4)

+ 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. [SOLVED] Formula to Provide Capped List of Sequential Numbers Based On Multiple Criteria
    By rlmh06 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-15-2017, 06:18 PM
  2. Capped Exponential Bonus Formula
    By P_Bennett in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-06-2015, 01:06 PM
  3. 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
  4. Replies: 4
    Last Post: 09-03-2013, 10:10 AM
  5. Formula to calculate a % of a capped value. Frustrated!
    By Jo19141 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-09-2013, 07:14 AM
  6. Replies: 3
    Last Post: 01-21-2013, 12:22 PM
  7. simple formula but with a capped value
    By ferriein in forum Excel General
    Replies: 3
    Last Post: 10-09-2008, 11:42 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