Forum Discussion
Find price "X" from a discounted and marked up Total Cost
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 |
- I agree it will be much better to hold the tax rate and discount amount in a reference cell that can be easily changed in the future. However, I did like seeing the power of Excel using complex formulas - thanks for demonstrating that.
Using your example, what I need to avoid is having to enter individual line items (like in your cell B2). My example only had one item, but most receipts have many items.
So, I would like to enter your B25 data in a cell with a formula in the same row (adjacent column) that will display the B11 result and one in another column (same row) for your B12 result. Both of these formulas can reference anchored cells (B3 and B6) that contain the discount amount and the tax amount.
I suppose the question is can I get to the B11 result without B2? Maybe starting with the B25 amount I would need a formula that removes the tax amount (B6) and then calculates the discount amount of the original price on that sum.
Great help. Thanks again.Did you look at the last three cells in my screenshot?
B25 has the total amount.
B26 has the discount amount
B27 has the original tax amount without discount.
Neither B26 nor B27 reference any cells other than B25.
Is that not what you want? Can you explain what you want instead?
- One of the spreadsheets I'm working with has 367 items and it continues to grow as the project progresses.
The sheet has reference cells "G$4" & "I$4" with the discount and tax amounts, respectfully.
Column "F" has Total amounts entered. Every formula in column "G" uses "G$4" and in column "I" the formulas reference "I$4". Column "I formulas are easy; it's the sales tax that is actually computed on the Totals in column "F".
Now I need the formulas in column "G" to calculate the discount of the original price using the Total entry in column "F", because the 'original price' is not entered anywhere in the sheet. I believe you may have that in one of your examples; I'll look at it again.