Forum Discussion
Find price "X" from a discounted and marked up Total Cost
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.
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.
Hello again,
yes, you can do it all without helper cells with just two formulas and hard wired discount and tax code, but why would you want to? The formula becomes very long and unwieldy and if the tax rate changes or the discount changes, then you need to edit the formula and make sure you catch all occurrences of the old value, which may lead to processing errors.
It is much easier to keep the tax and discount codes in separate cells and use a few helper columns for the calculations that need to be repeated in the formula.
But if you insist: The screenshot below has the single formula approach in the last two cells, based just on the total in cell B25. Everything else is calculated with hard-coded discount code of 0.1 and tax rate of 0.07.
for your reference: the formulas are
=((B25/(1+0.07))/(1-0.1))*0.1 =((B25/(1+0.07))+(((B25/(1+0.07))/(1-0.1))*0.1))*0.07
