+ Reply to Thread
Results 1 to 6 of 6

Calculating highest allowable discount

  1. #1
    Forum Contributor
    Join Date
    02-06-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    112

    Calculating highest allowable discount

    Hello All,
    Thank you for taking a quick moment to review my problem!
    On the attached workbook, in cell D44 on the "Template" workbook, you will see the number -705.52. This is the highest allowable additional discount a salesperson can enter when summing cells K33:K37. Here is what I'm after - F33 allows the salesperson to either enter a direct number discount by selecting "Amount" - say -700 OR if they select "%" they can enter a percentage. I want the sales person to not be able to enter a higher discount than cell D44 when summing cells K33:K37. For instance, lets say the product in row 33 has an amount discount of -700, and then in row 34 they enter another product - I do not want them to be able to discount this product in cell K34 more than $5.52. If they try to do this, they can get an error or it will only allow them to put in $5.52. Conversely, if a user wants to put in a "%" discount, they still cannot discount more than -705.52 in total when adding cells K33:K37.

    I hope my question is straight forward! I have been looking at this too long! Any and all help is much appreciated!

    Regards,
    Andrew
    Last edited by alansidman; 06-25-2020 at 11:57 AM.

  2. #2
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,616

    Re: Calculating highest allowable discount

    1) On the Policy worksheet you have an embedded MS-Word document that looks like it may be company private. You may want to delete this. If so, I'm not sure whether you can do this yourself. If not then contact a moderator to do it for you.

    2) To address your problem, it would be straightforward to have cell K38 display an error message instead of the total discount in the event that the total discount exceeds cell D44. Like this in K38:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    . I don't think this is exactly what you were looking for but is it workable for you?
    Geoff

    Did I help significantly? If you wish, click on * Add Reputation to say thanks.
    If your problem has been resolved please select ?Solved? from the Thread Tools menu

  3. #3
    Forum Contributor
    Join Date
    02-06-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    112

    Re: Calculating highest allowable discount

    Hi Geoff,
    You are correct, I accidentally posted confidential documents. Is there a quick way to have this taken down?
    Also, your solution is very, very good. Thank you!

  4. #4
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,616

    Re: Calculating highest allowable discount

    I have alerted a moderator to have the workbook deleted.
    Geoff

  5. #5
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Calculating highest allowable discount

    Workbook removed. If you wish to reload a workbook with dummy info, please do.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  6. #6
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,616

    Re: Calculating highest allowable discount

    Thanks Alan. I think we're all set. In post #3 the OP says the proposed solution is OK.

+ 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. Calculate discount but ignore highest value
    By ngocpdn in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-02-2019, 07:53 AM
  2. [SOLVED] Calculating Discount Price
    By kbyjg123 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 07-14-2017, 11:43 AM
  3. [SOLVED] Calculating discount amount with respect to hierarchy of additional discounts
    By acellis9 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-03-2014, 08:40 PM
  4. Calculating Discount
    By DebbieH in forum Excel General
    Replies: 3
    Last Post: 11-29-2011, 02:21 PM
  5. calculating discount 15 tiers
    By bamboozle in forum Excel General
    Replies: 2
    Last Post: 04-12-2010, 01:24 PM
  6. Calculating a Discount based on certain rules
    By mikeblackberry in forum Excel General
    Replies: 7
    Last Post: 02-11-2009, 06:12 PM
  7. Replies: 2
    Last Post: 05-07-2007, 12:12 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