Need formula to calculate subtotal before sales tax
 

I have a spreadsheet showing a year's worth of invoices that have the
final totals listed, but I need a formula to show the subtotal before
the sales tax is added.

The example I am working from has me very frustrated, to say the
least. I feel REALLY dumb right about now!

Subtotal of Order = $253
Sales Tax = 8.75%
Final Total = $275.14

So, my year summary already shows the total ($275.14) and I need to
calculate and display the subtotal ($253). I know the subtotal in
this example because I went back and looked at the original invoice,
but I don't want to do that every time.

I won't even bother including my formulas, as I already know they are
wrong!

Answer: Need formula to calculate subtotal before sales tax
 

Don't worry, calculating subtotals before sales tax can be tricky, but it's definitely doable in Excel. Here's how you can do it:

1. First, you need to know the sales tax rate for your invoices. Let's say it's 8.75%, like in your example.
   
2. Next, you need to divide the final total by 1 plus the sales tax rate. This will give you the subtotal before sales tax. In your example, the calculation would be:
   
   
   Formula:

   ---

   = $275.14/(1+8.75%) 


   ---

   This will give you the result of $253.00, which is the subtotal before sales tax.
   
3. To make this calculation easier for your entire year's worth of invoices, you can use a formula that references the final total and the sales tax rate. Assuming your final total is in column A and your sales tax rate is in column B, you can use the following formula in column C to calculate the subtotal before sales tax:
   
   
   Formula:

   ---

   = A2/(1+B2) 


   ---

   This formula assumes that your data starts in row 2. You can adjust the formula as needed for your specific data range.
   
4. Finally, you can format the subtotal column as currency to make it easier to read.
   

That's it! With this formula, you'll be able to quickly calculate subtotals before sales tax for all of your invoices.

Need formula to calculate subtotal before sales tax
 

Divide Total by 1.0875.

- David

HLR2007 wrote:
I have a spreadsheet showing a year's worth of invoices that have the
final totals listed, but I need a formula to show the subtotal before
the sales tax is added.

The example I am working from has me very frustrated, to say the
least. I feel REALLY dumb right about now!

Subtotal of Order = $253
Sales Tax = 8.75%
Final Total = $275.14

So, my year summary already shows the total ($275.14) and I need to
calculate and display the subtotal ($253). I know the subtotal in
this example because I went back and looked at the original invoice,
but I don't want to do that every time.

I won't even bother including my formulas, as I already know they are
wrong!

Need formula to calculate subtotal before sales tax
 

HLR --

Howze about:

A B
1 $275.14 =A1/1.0875

seems to work. Answer is $253.0023

HTH

"HLR2007" wrote:

I have a spreadsheet showing a year's worth of invoices that have the
final totals listed, but I need a formula to show the subtotal before
the sales tax is added.

The example I am working from has me very frustrated, to say the
least. I feel REALLY dumb right about now!

Subtotal of Order = $253
Sales Tax = 8.75%
Final Total = $275.14

So, my year summary already shows the total ($275.14) and I need to
calculate and display the subtotal ($253). I know the subtotal in
this example because I went back and looked at the original invoice,
but I don't want to do that every time.

I won't even bother including my formulas, as I already know they are
wrong!

Need formula to calculate subtotal before sales tax
 

On Sep 13, 8:48 pm, HLR2007 wrote:
I have a spreadsheet showing a year's worth of invoices that have the
final totals listed, but I need a formula to show the subtotal before
the sales tax is added.
[....]
The example I am working from [....]
Subtotal of Order = $253
Sales Tax = 8.75%
Final Total = $275.14

Ostensibly, if the final total is in B3 and the sales tax rate in B2,
then subtotal in B1 can be computed by:

=round(B3/(1+B2), 2)

But I don't know if that will always reproduce the original subtotal
exactly. You see, originally the final total (B3) was computed
effectively by:

=B1 + round(B1*B2, 2)

But it seems to work for all the (random) numbers that I have tried.

Need formula to calculate subtotal before sales tax
 

On Sep 13, 11:34 pm, pdberger
wrote:
A B
1 $275.14 =A1/1.0875

seems to work. Answer is $253.0023

HTH


This one works perfectly! Thanks so much.