Sales Tax Calculation
 

Hi,

What function or calculation could I use in a spreadsheet to calculate the
price of an item minus the local sales tax so that in the end the final
price would be a whole number. I would like to enter the local tax rate in
percent so I could figure out what the price for an item should be so in the
end the final price is $5, $10, $15 etc. Using Excel 2000.

Thanks,
Linda

Sales Tax Calculation
 

If the tax rate is in cell B1 and the final (total) price is in B2, use this
formula in cell B3 (or any other cell):
=ROUND(B2/(1+B1),2)
Roy

"LMB" wrote in message
...
Hi,

What function or calculation could I use in a spreadsheet to calculate the
price of an item minus the local sales tax so that in the end the final
price would be a whole number. I would like to enter the local tax rate
in percent so I could figure out what the price for an item should be so
in the end the final price is $5, $10, $15 etc. Using Excel 2000.

Thanks,
Linda

Sales Tax Calculation
 

LMB wrote:
Hi,

What function or calculation could I use in a spreadsheet to calculate the
price of an item minus the local sales tax so that in the end the final
price would be a whole number. I would like to enter the local tax rate in
percent so I could figure out what the price for an item should be so in the
end the final price is $5, $10, $15 etc. Using Excel 2000.

Thanks,
Linda

Hi Linda,

Another way to do it would be to format the cell where you enter your
local tax as Percentage (Right Click the cell and chose Format Cells -
Number and Chose Percentage on the right side). If we say that A1 is
the tax cell and B1 is the price including tax then i think that this
formula will give you the price net tax as a whole number:

=ROUND(B1-(B1*A1),0)

If you want the final price shown as currency then Right Click the cell
and chose Format Cells - Number and Chose Currency on the right side
and chose $ as symbol.

Regards,

Bondi

Sales Tax Calculation
 

Thanks Roy and Bondi. I am heading out on a trip and will try these
suggestions this weekend.

Linda

"LMB" wrote in message
...
Hi,

What function or calculation could I use in a spreadsheet to calculate the
price of an item minus the local sales tax so that in the end the final
price would be a whole number. I would like to enter the local tax rate
in percent so I could figure out what the price for an item should be so
in the end the final price is $5, $10, $15 etc. Using Excel 2000.

Thanks,
Linda

Sales Tax Calculation
 

I tried this formula but I'm not getting what I need. I need to enter the
tax rate and the final price and my calculation will give me the Item Price.

Tax Rate 7.25
Total Price 15.00
Item Price $1.82 (Calculated Number based on Tax Rate and Total Price)

Thanks,
Linda

"Roy Harrill" wrote in message
...
If the tax rate is in cell B1 and the final (total) price is in B2, use
this
formula in cell B3 (or any other cell):
=ROUND(B2/(1+B1),2)
Roy

"LMB" wrote in message
...
Hi,

What function or calculation could I use in a spreadsheet to calculate
the price of an item minus the local sales tax so that in the end the
final price would be a whole number. I would like to enter the local tax
rate in percent so I could figure out what the price for an item should
be so in the end the final price is $5, $10, $15 etc. Using Excel 2000.

Thanks,
Linda

Sales Tax Calculation
 

"Bondi" wrote in message
oups.com...

LMB wrote:
Hi,

What function or calculation could I use in a spreadsheet to calculate
the
price of an item minus the local sales tax so that in the end the final
price would be a whole number. I would like to enter the local tax rate
in
percent so I could figure out what the price for an item should be so in
the
end the final price is $5, $10, $15 etc. Using Excel 2000.

Thanks,
Linda

Hi Linda,

Another way to do it would be to format the cell where you enter your
local tax as Percentage (Right Click the cell and chose Format Cells -
Number and Chose Percentage on the right side). If we say that A1 is
the tax cell and B1 is the price including tax then i think that this
formula will give you the price net tax as a whole number:

=ROUND(B1-(B1*A1),0)

If you want the final price shown as currency then Right Click the cell
and chose Format Cells - Number and Chose Currency on the right side
and chose $ as symbol.

Regards,

Bondi


I tried this formula as well as the other one suggested but I'm still not
getting what I need. I need to enter the tax rate and the final price and
my calculation will give me the Item Price. I tried you suggestion and got
this. I am not the best at math but I think Tax on $14.00 is 1.015 so the
total price would be $15.02. I need the final price to be $15.00 even.

Tax Rate Total Price Item Price
7.25% $15.00 $14.00

Thanks

Sales Tax Calculation
 

LMB

Try this formula.........=ROUND(B2/(1+B1),1) returns 14.00

Note that tax rate in B1 is .0725 not 7.25


Gord Dibben MS Excel MVP

On Sat, 8 Jul 2006 18:07:24 -0400, "LMB" wrote:

I tried this formula but I'm not getting what I need. I need to enter the
tax rate and the final price and my calculation will give me the Item Price.

Tax Rate 7.25
Total Price 15.00
Item Price $1.82 (Calculated Number based on Tax Rate and Total Price)

Thanks,
Linda

"Roy Harrill" wrote in message
...
If the tax rate is in cell B1 and the final (total) price is in B2, use
this
formula in cell B3 (or any other cell):
=ROUND(B2/(1+B1),2)
Roy

"LMB" wrote in message
...
Hi,

What function or calculation could I use in a spreadsheet to calculate
the price of an item minus the local sales tax so that in the end the
final price would be a whole number. I would like to enter the local tax
rate in percent so I could figure out what the price for an item should
be so in the end the final price is $5, $10, $15 etc. Using Excel 2000.

Thanks,
Linda

Sales Tax Calculation
 

Thanks Gord,

Ok..that seems better but if I have an item the is a total price of $20.00
with sales tax .0725 <g, my Item Price calculates out to $18.60. If I plug
in 18.60 in a sales tax calculator, I get a total price of 19.95. Is the
Round part causing this?

Thanks,
Linda


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
LMB

Try this formula.........=ROUND(B2/(1+B1),1) returns 14.00

Note that tax rate in B1 is .0725 not 7.25


Gord Dibben MS Excel MVP

On Sat, 8 Jul 2006 18:07:24 -0400, "LMB"
wrote:

I tried this formula but I'm not getting what I need. I need to enter the
tax rate and the final price and my calculation will give me the Item
Price.

Tax Rate 7.25
Total Price 15.00
Item Price $1.82 (Calculated Number based on Tax Rate and Total Price)

Thanks,
Linda

"Roy Harrill" wrote in message
.. .
If the tax rate is in cell B1 and the final (total) price is in B2, use
this
formula in cell B3 (or any other cell):
=ROUND(B2/(1+B1),2)
Roy

"LMB" wrote in message
...
Hi,

What function or calculation could I use in a spreadsheet to calculate
the price of an item minus the local sales tax so that in the end the
final price would be a whole number. I would like to enter the local
tax
rate in percent so I could figure out what the price for an item should
be so in the end the final price is $5, $10, $15 etc. Using Excel
2000.

Thanks,
Linda

Sales Tax Calculation
 

Yes.

The rounding changes the actual value.

If you use this formula =(B2/(1+B1))

B1 = .0725

B2 = 20.00

Formula returns 18.65

..0725 * 18.65 = 20.00


Gord

On Sat, 8 Jul 2006 20:36:47 -0400, "LMB" wrote:

Thanks Gord,

Ok..that seems better but if I have an item the is a total price of $20.00
with sales tax .0725 <g, my Item Price calculates out to $18.60. If I plug
in 18.60 in a sales tax calculator, I get a total price of 19.95. Is the
Round part causing this?

Thanks,
Linda


"Gord Dibben" <gorddibbATshawDOTca wrote in message
.. .
LMB

Try this formula.........=ROUND(B2/(1+B1),1) returns 14.00

Note that tax rate in B1 is .0725 not 7.25


Gord Dibben MS Excel MVP

On Sat, 8 Jul 2006 18:07:24 -0400, "LMB"
wrote:

I tried this formula but I'm not getting what I need. I need to enter the
tax rate and the final price and my calculation will give me the Item
Price.

Tax Rate 7.25
Total Price 15.00
Item Price $1.82 (Calculated Number based on Tax Rate and Total Price)

Thanks,
Linda

"Roy Harrill" wrote in message
. ..
If the tax rate is in cell B1 and the final (total) price is in B2, use
this
formula in cell B3 (or any other cell):
=ROUND(B2/(1+B1),2)
Roy

"LMB" wrote in message
...
Hi,

What function or calculation could I use in a spreadsheet to calculate
the price of an item minus the local sales tax so that in the end the
final price would be a whole number. I would like to enter the local
tax
rate in percent so I could figure out what the price for an item should
be so in the end the final price is $5, $10, $15 etc. Using Excel
2000.

Thanks,
Linda







Gord Dibben MS Excel MVP

Sales Tax Calculation
 

Thanks...I could have just tried it...geesh, I got myself all
confused....not hard to do.

Linda

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Yes.

The rounding changes the actual value.

If you use this formula =(B2/(1+B1))

B1 = .0725

B2 = 20.00

Formula returns 18.65

.0725 * 18.65 = 20.00


Gord

On Sat, 8 Jul 2006 20:36:47 -0400, "LMB"
wrote:

Thanks Gord,

Ok..that seems better but if I have an item the is a total price of $20.00
with sales tax .0725 <g, my Item Price calculates out to $18.60. If I
plug
in 18.60 in a sales tax calculator, I get a total price of 19.95. Is the
Round part causing this?

Thanks,
Linda


"Gord Dibben" <gorddibbATshawDOTca wrote in message
. ..
LMB

Try this formula.........=ROUND(B2/(1+B1),1) returns 14.00

Note that tax rate in B1 is .0725 not 7.25


Gord Dibben MS Excel MVP

On Sat, 8 Jul 2006 18:07:24 -0400, "LMB"
wrote:

I tried this formula but I'm not getting what I need. I need to enter
the
tax rate and the final price and my calculation will give me the Item
Price.

Tax Rate 7.25
Total Price 15.00
Item Price $1.82 (Calculated Number based on Tax Rate and Total Price)

Thanks,
Linda

"Roy Harrill" wrote in message
.. .
If the tax rate is in cell B1 and the final (total) price is in B2,
use
this
formula in cell B3 (or any other cell):
=ROUND(B2/(1+B1),2)
Roy

"LMB" wrote in message
...
Hi,

What function or calculation could I use in a spreadsheet to
calculate
the price of an item minus the local sales tax so that in the end the
final price would be a whole number. I would like to enter the local
tax
rate in percent so I could figure out what the price for an item
should
be so in the end the final price is $5, $10, $15 etc. Using Excel
2000.

Thanks,
Linda







Gord Dibben MS Excel MVP