Forum Discussion
Find price "X" from a discounted and marked up Total Cost
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?
- 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.
- 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.