How to calculate Sales Tax and Service charge from an invoice total without a subtotal

I am attaching a spreadsheet with an example on row 1 of how our point of sale calculates the invoice total from the sale total

Essentially it arrives at a subtotal of all sales then multiplies that sale subtotal by the tax percentage to get the sales tax and the service charge percentage to arrive at the service charge. It then adds the calculated tax and calculated service charge to the sale subtotal to arrive at the invoice total.

We have records with the invoice total and know the tax percentage and service charge percentage. We need to back into the correct tax and service charge amounts then subtract them from the invoice total to get a sales subtotal.

Is this possible or are we missing too many variables?

Thanks!

Thanks for your help! Worked like a charm.

Hi JeteMC,

I have the same issue, however for me, the 22% service charge is also taxed (tax rate of 6.75%, that I can change myself though.)

Is it possible to also do this in excel, or is it too many variables at once? I tried to simply add a cell that calculates the tax on the Service Charge and then add that cell to your formula in A5, but I get the dreaded circular references error. Any help you could provide would be appreciated.