Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old 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
Default 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!


  #2   Report Post  
Old 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
Default 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!

  #3   Report Post  
Old 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
Default 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!


  #4   Report Post  
Old 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
Default 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.

  #5   Report Post  
Old 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
Default 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.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculate Sales Amount lucrezia Excel Discussion (Misc queries) 3 October 6th 06 08:22 PM
HELP! Single cell formula to calculate weeks cover of stock on forward sales. [email protected] Excel Worksheet Functions 2 January 13th 06 11:24 AM
formula to calculate sales tax from total sales Deanna Excel Worksheet Functions 7 October 5th 05 08:57 PM
calculate predicted sales value Vincci Excel Worksheet Functions 0 June 1st 05 09:34 AM
how do I create a formula to calculate sales tax using Yes & No Kel Excel Worksheet Functions 2 April 14th 05 12:23 AM


All times are GMT +1. The time now is 09:31 PM.

Powered by vBulletin® Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017