Forum Discussion
Find price "X" from a discounted and marked up Total Cost
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.
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.
- 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.
Sorry, I don't understand your last statement.
If you want two numbers, then you will need two formulas. I have split it into three in my reply, so it does not look too confusing, but you can of course have just two self-sufficient formulas that don't rely on other cells. Just replace the reference to E11 or F11 with the actual formula in E11 or F11. The result will not be pretty, which is why it is often easier to create a helper cell with an intermediate step and cascade formulas that reference previous cells.
Thanks for your help. I enjoy working with Excel; always something new to learn.
It might be best to let you look at a sample of the input items (receipts) I'm working with on this task and you might have a better path to a solution. I'll attach a copy.
I would like to use the 'Total' on the receipt as the only numeric data entry in my spreadsheet; just trying to be expedient.
It seems like I will need to use two formulas. In your example you discounted the subtotal by 10% in cell C8. I need to store the 'Retail Subtotal' discount amount (not the subtotal) using a formula. Then I can use a standard operator to express the 7% tax markup formula in a third column because it is based on the discounted Subtotal.
