Forum Discussion
Find price "X" from a discounted and marked up Total Cost
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?
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.
- Thanks; discount is good, but tax result is $0.51 for the $7.21 Total example and the actual receipt shows $0.48. The tax is calculated on the discounted subtotal not the Total. Could that be the problem?
OK.
Here's a screenshot of your setup as you describe it in your last post.
Totals in column F. Discount in cell G4, tax rate in cell I4. The last two cells have the range names "discount" and "tax" respectively, so they are easy to spot in the fomula.
Now, in row 8 I have my first total value. In your spreadsheet that may be a different row, so adjust the row number of the formula accordingly.
In cell G8 I have the formula
=((F8/(1+tax))/(1-discount))*discount
which can be copied down.
In cell I8 I have the formula
=((F8/(1+discount))+(((F8/(1+tax))/(1-discount))*discount))*tax
which can be copied down.
I only re-arranged the formulas I posted previously. The logic and the maths is still the same.
Does that do what you want?
I attach the file for you to play with.