Sales Tax Calculation

[LMB]

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

[Roy Harrill]

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[Bondi]

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

[LMB]

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

Linda

"LMB" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[LMB]

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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>
>

[LMB]

"Bondi" <[email protected]> wrote in message
news:[email protected]...
>
> 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

[Gord Dibben]

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" <[email protected]> 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" <[email protected]> wrote in message
>news:%[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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
>>>
>>
>>
>

[LMB]

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
news:[email protected]...
> 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" <[email protected]>
> 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" <[email protected]> wrote in message
>>news:%[email protected]...
>>> 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" <[email protected]> wrote in message
>>> news:[email protected]...
>>>> 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]

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" <[email protected]> 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
>news:[email protected]...
>> 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" <[email protected]>
>> 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" <[email protected]> wrote in message
>>>news:%[email protected]...
>>>> 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" <[email protected]> wrote in message
>>>> news:[email protected]...
>>>>> 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

[LMB]

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

Linda

"Gord Dibben" <gorddibbATshawDOTca> wrote in message
news:[email protected]...
> 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" <[email protected]>
> 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
>>news:[email protected]...
>>> 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" <[email protected]>
>>> 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" <[email protected]> wrote in message
>>>>news:%[email protected]...
>>>>> 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" <[email protected]> wrote in message
>>>>> news:[email protected]...
>>>>>> 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

[LMB]

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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>
>

[LMB]

"Bondi" <[email protected]> wrote in message
news:[email protected]...
>
> 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

[Gord Dibben]

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" <[email protected]> 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" <[email protected]> wrote in message
>news:%[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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
>>>
>>
>>
>

[LMB]

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
news:[email protected]...
> 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" <[email protected]>
> 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" <[email protected]> wrote in message
>>news:%[email protected]...
>>> 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" <[email protected]> wrote in message
>>> news:[email protected]...
>>>> 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]

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" <[email protected]> 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
>news:[email protected]...
>> 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" <[email protected]>
>> 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" <[email protected]> wrote in message
>>>news:%[email protected]...
>>>> 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" <[email protected]> wrote in message
>>>> news:[email protected]...
>>>>> 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

[LMB]

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

Linda

"Gord Dibben" <gorddibbATshawDOTca> wrote in message
news:[email protected]...
> 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" <[email protected]>
> 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
>>news:[email protected]...
>>> 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" <[email protected]>
>>> 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" <[email protected]> wrote in message
>>>>news:%[email protected]...
>>>>> 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" <[email protected]> wrote in message
>>>>> news:[email protected]...
>>>>>> 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