Forum Discussion

Tom David's avatar
Tom David
Copper Contributor
Feb 18, 2018

Find price "X" from a discounted and marked up Total Cost

Task: cataloging receipts from expenditures.

Have receipts with multiple items listed. The subtotal has been discounted from the original retail price by 10%. That discounted subtotal is then marked up by 7% (tax).

Would like to make only one numeric entry (the amount paid) and have formulas in adjacent columns display the amount of the 10% discount of the original price and the amount of the 7% markup of the original price (not the discounted subtotal).

  • Hello,

     

    are you struggling with the Excel formulas to use or are you asking about the underlying maths to calculate the result? In Excel you can use the regular operators for addition (+), subtraction (-), multiplication (*) and division (/), so use the same approach you would use when calculating by hand. 

     

    I'm not quite clear what that result should be, so it would help if you could post a data sample, fill in the desired result manually and explain in context.

    • Tom David's avatar
      Tom David
      Copper Contributor
      I'm looking for a formula... the issue is trying to input only the Total (which has been discounted 10% and the result marked up 7%) and expressing the discount amount of the retail price.
    • IngeborgHawighorst's avatar
      IngeborgHawighorst
      MVP

      I've put together a data sample and some formulas.

      The highlighted cells have the range names "discount", "markup" and "total", respectively.

       

      The formulas are

      Net before markup: 

      =total/(1+markup)

       

      Discount amount (where E11 is "Net before markup)"

      =(E11/(1-discount))*discount

       

      Original markup (where F11 is "Discount amount")

      =(E11+F11)*markup

       

       

      let me know if that helps.

       

       

      • Tom David's avatar
        Tom David
        Copper Contributor
        Yes, as I suspected, it looks like I will need to handle the discount and markup total separately. It's a nice challenge. I'll work on it later and reply with my thoughts for a solution. Thanks.

Resources