Sales Tax Calculation "LMB" <RomulanQueen[ at ]10Forward.SSTNG> 05.07.2006 02:29:29 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

Re: Sales Tax Calculation "Roy Harrill" <hooroy[ at ]adelphia.net> 05.07.2006 02:49:01 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" <RomulanQueen[ at ]10Forward.SSTNG> wrote in message news:eNxjZr9nGHA.4248[ at ]TK2MSFTNGP05.phx.gbl... [Quoted Text] > 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 >

Re: Sales Tax Calculation "Bondi" <mbondorff[ at ]hotmail.com> 05.07.2006 09:24:00 LMB wrote: [Quoted Text] > 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

Re: Sales Tax Calculation "LMB" <RomulanQueen[ at ]10Forward.SSTNG> 06.07.2006 13:06:53 Thanks Roy and Bondi. I am heading out on a trip and will try these suggestions this weekend. Linda "LMB" <RomulanQueen[ at ]10Forward.SSTNG> wrote in message news:eNxjZr9nGHA.4248[ at ]TK2MSFTNGP05.phx.gbl... [Quoted Text] > 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 >

Re: Sales Tax Calculation "LMB" <RomulanQueen[ at ]10Forward.SSTNG> 08.07.2006 22:07:24 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" <hooroy[ at ]adelphia.net> wrote in message news:%23n$RS29nGHA.3440[ at ]TK2MSFTNGP04.phx.gbl... [Quoted Text] > 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" <RomulanQueen[ at ]10Forward.SSTNG> wrote in message > news:eNxjZr9nGHA.4248[ at ]TK2MSFTNGP05.phx.gbl... >> 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 >> > >

Re: Sales Tax Calculation "LMB" <RomulanQueen[ at ]10Forward.SSTNG> 08.07.2006 22:18:47 "Bondi" <mbondorff[ at ]hotmail.com> wrote in message news:1152091440.250991.276960[ at ]v61g2000cwv.googlegroups.com... [Quoted Text] > > 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

Re: Sales Tax Calculation Gord Dibben <gorddibbATshawDOTca> 08.07.2006 22:19:39 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" <RomulanQueen[ at ]10Forward.SSTNG> wrote: [Quoted Text] >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" <hooroy[ at ]adelphia.net> wrote in message >news:%23n$RS29nGHA.3440[ at ]TK2MSFTNGP04.phx.gbl... >> 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" <RomulanQueen[ at ]10Forward.SSTNG> wrote in message >> news:eNxjZr9nGHA.4248[ at ]TK2MSFTNGP05.phx.gbl... >>> 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 >>> >> >> >

Re: Sales Tax Calculation "LMB" <RomulanQueen[ at ]10Forward.SSTNG> 09.07.2006 00:36:47 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:oob0b2tdi9mpa6vn491v364viamnbb56b4[ at ]4ax.com... [Quoted Text] > 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" <RomulanQueen[ at ]10Forward.SSTNG> > 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" <hooroy[ at ]adelphia.net> wrote in message >>news:%23n$RS29nGHA.3440[ at ]TK2MSFTNGP04.phx.gbl... >>> 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" <RomulanQueen[ at ]10Forward.SSTNG> wrote in message >>> news:eNxjZr9nGHA.4248[ at ]TK2MSFTNGP05.phx.gbl... >>>> 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 >>>> >>> >>> >> >

Re: Sales Tax Calculation Gord Dibben <gorddibbATshawDOTca> 09.07.2006 01:02:44 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" <RomulanQueen[ at ]10Forward.SSTNG> wrote: [Quoted Text] >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:oob0b2tdi9mpa6vn491v364viamnbb56b4[ at ]4ax.com... >> 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" <RomulanQueen[ at ]10Forward.SSTNG> >> 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" <hooroy[ at ]adelphia.net> wrote in message >>>news:%23n$RS29nGHA.3440[ at ]TK2MSFTNGP04.phx.gbl... >>>> 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" <RomulanQueen[ at ]10Forward.SSTNG> wrote in message >>>> news:eNxjZr9nGHA.4248[ at ]TK2MSFTNGP05.phx.gbl... >>>>> 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

Re: Sales Tax Calculation "LMB" <RomulanQueen[ at ]10Forward.SSTNG> 09.07.2006 01:25:59 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:93l0b212nv1g1ipopdhodrrcm41vmkd0bg[ at ]4ax.com... [Quoted Text] > 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" <RomulanQueen[ at ]10Forward.SSTNG> > 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:oob0b2tdi9mpa6vn491v364viamnbb56b4[ at ]4ax.com... >>> 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" <RomulanQueen[ at ]10Forward.SSTNG> >>> 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" <hooroy[ at ]adelphia.net> wrote in message >>>>news:%23n$RS29nGHA.3440[ at ]TK2MSFTNGP04.phx.gbl... >>>>> 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" <RomulanQueen[ at ]10Forward.SSTNG> wrote in message >>>>> news:eNxjZr9nGHA.4248[ at ]TK2MSFTNGP05.phx.gbl... >>>>>> 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