Remember Me?

#1
September 14th 07, 04:48 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Sep 2007 Posts: 2
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 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!

#2
September 14th 07, 05:30 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2007 Posts: 84
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 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!

#3
September 14th 07, 05:34 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 258
Need formula to calculate subtotal before sales tax

HLR --

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 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!

#4
September 14th 07, 05:56 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jan 2007 Posts: 2,059
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 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.

#5
September 15th 07, 04:28 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Sep 2007 Posts: 2
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.

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post lucrezia Excel Discussion (Misc queries) 3 October 6th 06 08:22 PM [email protected] Excel Worksheet Functions 2 January 13th 06 10:24 AM Deanna Excel Worksheet Functions 7 October 5th 05 08:57 PM Vincci Excel Worksheet Functions 0 June 1st 05 09:34 AM Kel Excel Worksheet Functions 2 April 14th 05 12:23 AM

All times are GMT +1. The time now is 10:06 PM.