How do I apply State O Maine 5% Sales Tax schedule to $ amounts.

Maine 5% Sales Tax Schedule
 
From              To            Tax
$0.00            $0.10        $0.00
$0.11            $0.20        $0.01
$0.21            $0.40        $0.02
$0.41            $0.60        $0.03
$0.61            $0.80        $0.04
$0.81            $1.00        $0.05

If you normaly multiple 10 cents by .05 it equals $0.01.... That would be 
wrong under their tax schedule. Can anybody help me? Thank you
0
Utf
4/20/2010 9:05:01 PM
excel.programming 6508 articles. 1 followers. Follow

16 Replies
656 Views

Similar Articles

[PageSpeed] 59

Something like this.  For the Case Else section, adjust the formula as needed.

Function MainTax(myCost As Double)

    Select Case myCost
        Case Is <= 0.1: MainTax = 0
        Case Is <= 0.2: MainTax = 0.01
        Case Is <= 0.4: MainTax = 0.02
        Case Is <= 0.6: MainTax = 0.03
        Case Is <= 0.8: MainTax = 0.04
        Case Is <= 1: MainTax = 0.05
        Case Else
            MainTax = Round(myCost * 0.05, 2)
    End Select

End Function


"Bamforth" wrote:

> Maine 5% Sales Tax Schedule
>  
> From              To            Tax
> $0.00            $0.10        $0.00
> $0.11            $0.20        $0.01
> $0.21            $0.40        $0.02
> $0.41            $0.60        $0.03
> $0.61            $0.80        $0.04
> $0.81            $1.00        $0.05
> 
> If you normaly multiple 10 cents by .05 it equals $0.01.... That would be 
> wrong under their tax schedule. Can anybody help me? Thank you
0
Utf
4/20/2010 10:06:01 PM
Somehow it always takes posting my reply to realize I should have added 
something or done it slightly differently.  It looks like maybe your Case 
Else formula should be RoundUp(myCost * .05, 2)

"B Lynn B" wrote:

> Something like this.  For the Case Else section, adjust the formula as needed.
> 
> Function MainTax(myCost As Double)
> 
>     Select Case myCost
>         Case Is <= 0.1: MainTax = 0
>         Case Is <= 0.2: MainTax = 0.01
>         Case Is <= 0.4: MainTax = 0.02
>         Case Is <= 0.6: MainTax = 0.03
>         Case Is <= 0.8: MainTax = 0.04
>         Case Is <= 1: MainTax = 0.05
>         Case Else
>             MainTax = Round(myCost * 0.05, 2)
>     End Select
> 
> End Function
> 
> 
> "Bamforth" wrote:
> 
> > Maine 5% Sales Tax Schedule
> >  
> > From              To            Tax
> > $0.00            $0.10        $0.00
> > $0.11            $0.20        $0.01
> > $0.21            $0.40        $0.02
> > $0.41            $0.60        $0.03
> > $0.61            $0.80        $0.04
> > $0.81            $1.00        $0.05
> > 
> > If you normaly multiple 10 cents by .05 it equals $0.01.... That would be 
> > wrong under their tax schedule. Can anybody help me? Thank you
0
Utf
4/20/2010 10:11:02 PM
=ROUND((Amt+0.1*(MOD(Amt,1)>0.2))*0.0499,2)

0
Herbert
4/20/2010 11:03:00 PM
"Bamforth" wrote:
> Subject: How do I apply State O Maine 5% Sales Tax
> schedule to $ amounts.

It is hard to say for sure without seeing the rest of the tax table, at 
least through $2.  Assuming tax of $0.06 an amounts between $1.01 and $1.20, 
I would say:

=(A1>0.1)*ROUNDUP(A1*5%,2)

Note:  This assumes that A1 is already rounded to the penny; that is, 
=ROUND(formula,2).


----- original message -----

"Bamforth" wrote:
> Maine 5% Sales Tax Schedule
>  
> From              To            Tax
> $0.00            $0.10        $0.00
> $0.11            $0.20        $0.01
> $0.21            $0.40        $0.02
> $0.41            $0.60        $0.03
> $0.61            $0.80        $0.04
> $0.81            $1.00        $0.05
> 
> If you normaly multiple 10 cents by .05 it equals $0.01.... That would be 
> wrong under their tax schedule. Can anybody help me? Thank you
0
Utf
4/20/2010 11:35:01 PM
I wrote:
> =(A1>0.1)*ROUNDUP(A1*5%,2)

Nothing wrong with that.  But on second thought, the following is probably 
clearer and maybe even more efficient:

=IF(A1<=0.1, 0, ROUNDUP(A1*5%,2))


----- original message -----

"Joe User" wrote:
> "Bamforth" wrote:
> > Subject: How do I apply State O Maine 5% Sales Tax
> > schedule to $ amounts.
> 
> It is hard to say for sure without seeing the rest of the tax table, at 
> least through $2.  Assuming tax of $0.06 an amounts between $1.01 and $1.20, 
> I would say:
> 
> =(A1>0.1)*ROUNDUP(A1*5%,2)
> 
> Note:  This assumes that A1 is already rounded to the penny; that is, 
> =ROUND(formula,2).
> 
> 
> ----- original message -----
> 
> "Bamforth" wrote:
> > Maine 5% Sales Tax Schedule
> >  
> > From              To            Tax
> > $0.00            $0.10        $0.00
> > $0.11            $0.20        $0.01
> > $0.21            $0.40        $0.02
> > $0.41            $0.60        $0.03
> > $0.61            $0.80        $0.04
> > $0.81            $1.00        $0.05
> > 
> > If you normaly multiple 10 cents by .05 it equals $0.01.... That would be 
> > wrong under their tax schedule. Can anybody help me? Thank you
0
Utf
4/20/2010 11:48:01 PM
Try $1.10

0
Herbert
4/21/2010 3:24:38 AM
"Herbert Seidenberg" <herbds7-msxls@yahoo.com> wrote:
> Try $1.10

I did.  Try reading the assumptions in my posting. The OP failed to tell us 
how sales tax is computed above $1.  So I wrote:  "Assuming tax of $0.06 an 
amounts between $1.01 and $1.20".

And IF(A1<=0.1,0,ROUNDUP(A1*5%,2)) is indeed $0.06.

However, I have since looked up the Maine sales tax information [1].  I 
learned that the tax table included by the OP applies only to amounts of 
"less than" [sic] $1.

For amounts over $1, the rule is:  "Where the tax to be paid includes a 
fraction of one cent, the fraction is not required to be paid where it is 
less than one-half cent. A full cent, however, must be paid where the 
fraction is one-half cent or more."

So it appears that the correct formula is:

=IF(A1<=0.1,0,IF(A1<=1,ROUNDUP(A1*5%,2),ROUND(A1*5%,2)))

("<=1" matches the column title of the Maine tax tables.  And "<1" makes no 
difference.)

So ironically, tax on $1.10 is indeed $0.06.

(But tax on $1.01 to $1.09 is $0.05.  New information, new results!)


-----
Endnotes

[1] http://www.maine.gov/revenue/salesuse/july2000charts.pdf 

0
Joe
4/21/2010 6:53:45 AM
First and foremost, I want to thank you for your time and effort. However, 
tax on $1.10 is just $.05 not $.06...Therein lies my problem. The tax 
schedule does not change above $1.00. The same calculations apply. But before 
I get carried away, I'm going to try your formula first. Once again, thank 
you very much.

"Joe User" wrote:

> "Herbert Seidenberg" <herbds7-msxls@yahoo.com> wrote:
> > Try $1.10
> 
> I did.  Try reading the assumptions in my posting. The OP failed to tell us 
> how sales tax is computed above $1.  So I wrote:  "Assuming tax of $0.06 an 
> amounts between $1.01 and $1.20".
> 
> And IF(A1<=0.1,0,ROUNDUP(A1*5%,2)) is indeed $0.06.
> 
> However, I have since looked up the Maine sales tax information [1].  I 
> learned that the tax table included by the OP applies only to amounts of 
> "less than" [sic] $1.
> 
> For amounts over $1, the rule is:  "Where the tax to be paid includes a 
> fraction of one cent, the fraction is not required to be paid where it is 
> less than one-half cent. A full cent, however, must be paid where the 
> fraction is one-half cent or more."
> 
> So it appears that the correct formula is:
> 
> =IF(A1<=0.1,0,IF(A1<=1,ROUNDUP(A1*5%,2),ROUND(A1*5%,2)))
> 
> ("<=1" matches the column title of the Maine tax tables.  And "<1" makes no 
> difference.)
> 
> So ironically, tax on $1.10 is indeed $0.06.
> 
> (But tax on $1.01 to $1.09 is $0.05.  New information, new results!)
> 
> 
> -----
> Endnotes
> 
> [1] http://www.maine.gov/revenue/salesuse/july2000charts.pdf 
> 
> .
> 
0
Utf
4/22/2010 12:43:02 PM
On Tue, 20 Apr 2010 15:11:02 -0700, B Lynn B <BLynnB@discussions.microsoft.com>
wrote:

>Somehow it always takes posting my reply to realize I should have added 
>something or done it slightly differently.  It looks like maybe your Case 
>Else formula should be RoundUp(myCost * .05, 2)

The State of Maine Sales tax is 5%, with arithmetic rounding.  The 5% sales tax
table is inconsistent with the instructions which read 

"Where the tax to be paid includes a fraction of one cent, the fraction is not
required to be paid where it is less than one-half cent. A full cent, however,
must be paid where the fraction is one-half cent or more."

If that is true, then the formula would be simply:

=ROUND(A1*5%,2)

On the other hand, if the Table takes precedence, then:

=INT(A1)*5% +
VLOOKUP(MOD(A1,1),{0,0;0.11,0.01;0.21,0.02;0.41,0.03;0.61,0.04;0.81,0.05},2)

should give you the "table" answer.
--ron
0
Ron
4/22/2010 2:18:30 PM
"Bamforth" wrote: 
> tax on $1.10 is just $.05 not $.06.  [....] The tax 
> schedule does not change above $1.00. The same
> calculations apply. 

If that is the case, then perhaps the following formula meets your 
expectations (also see notes [1] and [2] below):

=IF(MOD(A1,1)<=0.1, INT(A1)*5%,
ROUNDUP(A1*5%,2))

However, for my edification, I would appreciate it if you could provide 
dispositive information (i.e. from the state of Maine) that confirms your 
expectation for $1.10.

As I noted, the Maine website [3] explains that the table you provided 
applies to "sales of less than $1.00" [sic].  My interpretation is:  it does 
not apply to sales of more than $1.

Following that table is a table for $1 to $100.  Unfortunately, entries are 
in increments of whole dollars.  However, preceding that table is the 
statement:  "Where the tax to be paid includes a fraction of one cent, the 
fraction is not required to be paid where it is less than one-half cent.  A 
full cent, however, must be paid where the fraction is one-half cent or more".

My interpretation is:  the __next__ "full cent ... must be paid where the 
fraction is one-half cent or more".  That describes normal rounding.

And my interpretation is:  that statement applies to the following table ($1 
to $100), since it obviously does not apply to the preceding table (less than 
$1).

Note that $1.10 times 5% is 5.5 cents ($0.055).  According to my 
interpretation of the statement above the $1-100 table, that should be 
rounded to 6 cents ($0.06) -- the "next full cent". 

Oddly, I cannot find any instructions for how to handle fractional cents on 
any of the Maine sales and use tax returns [4]. 


-----
Endnotes:

[1] Instead of MOD(A1,1), you might want to write A1-INT(A1).  There are 
defects in the Excel MOD function, at least in Excel 2003 and before.  I 
don't recall if those defects would apply to MOD(A1,1).

[2] Instead of INT(A1)*5%, you might want to write ROUND(INT(A1)*5%,2).  It 
should not be necessary when the tax rate is 5%.  But it could be needed for 
other tax rates.

[3] http://www.maine.gov/revenue/salesuse/july2000charts.pdf

[4] http://www.maine.gov/revenue/salesuse/returnlink.htm


----- original message -----

"Bamforth" wrote:
> First and foremost, I want to thank you for your time and effort. However, 
> tax on $1.10 is just $.05 not $.06...Therein lies my problem. The tax 
> schedule does not change above $1.00. The same calculations apply. But before 
> I get carried away, I'm going to try your formula first. Once again, thank 
> you very much.
> 
> "Joe User" wrote:
> 
> > "Herbert Seidenberg" <herbds7-msxls@yahoo.com> wrote:
> > > Try $1.10
> > 
> > I did.  Try reading the assumptions in my posting. The OP failed to tell us 
> > how sales tax is computed above $1.  So I wrote:  "Assuming tax of $0.06 an 
> > amounts between $1.01 and $1.20".
> > 
> > And IF(A1<=0.1,0,ROUNDUP(A1*5%,2)) is indeed $0.06.
> > 
> > However, I have since looked up the Maine sales tax information [1].  I 
> > learned that the tax table included by the OP applies only to amounts of 
> > "less than" [sic] $1.
> > 
> > For amounts over $1, the rule is:  "Where the tax to be paid includes a 
> > fraction of one cent, the fraction is not required to be paid where it is 
> > less than one-half cent. A full cent, however, must be paid where the 
> > fraction is one-half cent or more."
> > 
> > So it appears that the correct formula is:
> > 
> > =IF(A1<=0.1,0,IF(A1<=1,ROUNDUP(A1*5%,2),ROUND(A1*5%,2)))
> > 
> > ("<=1" matches the column title of the Maine tax tables.  And "<1" makes no 
> > difference.)
> > 
> > So ironically, tax on $1.10 is indeed $0.06.
> > 
> > (But tax on $1.01 to $1.09 is $0.05.  New information, new results!)
> > 
> > 
> > -----
> > Endnotes
> > 
> > [1] http://www.maine.gov/revenue/salesuse/july2000charts.pdf 
0
Utf
4/22/2010 2:28:02 PM
On Tue, 20 Apr 2010 23:53:45 -0700, "Joe User" <joeu2004> wrote:

>"Herbert Seidenberg" <herbds7-msxls@yahoo.com> wrote:
>> Try $1.10
>
>I did.  Try reading the assumptions in my posting. The OP failed to tell us 
>how sales tax is computed above $1.  So I wrote:  "Assuming tax of $0.06 an 
>amounts between $1.01 and $1.20".
>
>And IF(A1<=0.1,0,ROUNDUP(A1*5%,2)) is indeed $0.06.
>
>However, I have since looked up the Maine sales tax information [1].  I 
>learned that the tax table included by the OP applies only to amounts of 
>"less than" [sic] $1.
>
>For amounts over $1, the rule is:  "Where the tax to be paid includes a 
>fraction of one cent, the fraction is not required to be paid where it is 
>less than one-half cent. A full cent, however, must be paid where the 
>fraction is one-half cent or more."
>
>So it appears that the correct formula is:
>
>=IF(A1<=0.1,0,IF(A1<=1,ROUNDUP(A1*5%,2),ROUND(A1*5%,2)))
>
>("<=1" matches the column title of the Maine tax tables.  And "<1" makes no 
>difference.)
>
>So ironically, tax on $1.10 is indeed $0.06.
>
>(But tax on $1.01 to $1.09 is $0.05.  New information, new results!)
>
>
>-----
>Endnotes
>
>[1] http://www.maine.gov/revenue/salesuse/july2000charts.pdf 

I think it's more complicated.

I see a discrepancy between the written information and the table.

The written rules seem to be a straight 5% with simple "arithmetic rounding".

Using the table, however, gives different results.  And it seems to me that the
fractional dollar table also applies to amounts over $1:

"This table shows tax to $100 by units of $1.00. The tax to be collected is the
amount indicated below^ for each even dollar of the sale price plus the amount
indicated above* for the remainder of the sale price."

^below = the whole dollar table
*above = the fractional dollar table.

For example:  $1.25

From the even dollar table: $0.05
From the fractional table:  $0.02

Your formula will give results inconsistent with the table for a number of
values.

At least, the whole dollar table seems to be straight 5%.
--ron
0
Ron
4/22/2010 2:29:36 PM
Errata....

I wrote:
> My interpretation is:  the __next__ "full cent ... must
> be paid where the fraction is one-half cent or more".
> That describes normal rounding.

As Ron points out, I overlooked the instructions immediately above the 
$1-100 table, which does indeed match Bamforth's expectations, to wit:  "The 
tax to be collected is the amount indicated below for each even dollar of the 
sale price plus the amount indicated above for the remained of the sale 
price".

So I do now believe the following formula, included the previous response, 
does indeed provide the sales tax consistent with that rule:

=IF(MOD(A1,1)<=0.1, INT(A1)*5%,
ROUNDUP(A1*5%,2))

Note:  I believe the above is computationally equivalent to a literal 
formulation of the above rule.  I tested with several amounts.  But if you 
prefer:

=INT(A1)*5%
+IF(MOD(A1,1)<=0.1, 0, ROUNDUP(MOD(A1,1)*5%,2))


----- original message -----

"Joe User" wrote:
> "Bamforth" wrote: 
> > tax on $1.10 is just $.05 not $.06.  [....] The tax 
> > schedule does not change above $1.00. The same
> > calculations apply. 
> 
> If that is the case, then perhaps the following formula meets your 
> expectations (also see notes [1] and [2] below):
> 
> =IF(MOD(A1,1)<=0.1, INT(A1)*5%,
> ROUNDUP(A1*5%,2))
> 
> However, for my edification, I would appreciate it if you could provide 
> dispositive information (i.e. from the state of Maine) that confirms your 
> expectation for $1.10.
> 
> As I noted, the Maine website [3] explains that the table you provided 
> applies to "sales of less than $1.00" [sic].  My interpretation is:  it does 
> not apply to sales of more than $1.
> 
> Following that table is a table for $1 to $100.  Unfortunately, entries are 
> in increments of whole dollars.  However, preceding that table is the 
> statement:  "Where the tax to be paid includes a fraction of one cent, the 
> fraction is not required to be paid where it is less than one-half cent.  A 
> full cent, however, must be paid where the fraction is one-half cent or more".
> 
> My interpretation is:  the __next__ "full cent ... must be paid where the 
> fraction is one-half cent or more".  That describes normal rounding.
> 
> And my interpretation is:  that statement applies to the following table ($1 
> to $100), since it obviously does not apply to the preceding table (less than 
> $1).
> 
> Note that $1.10 times 5% is 5.5 cents ($0.055).  According to my 
> interpretation of the statement above the $1-100 table, that should be 
> rounded to 6 cents ($0.06) -- the "next full cent". 
> 
> Oddly, I cannot find any instructions for how to handle fractional cents on 
> any of the Maine sales and use tax returns [4]. 
> 
> 
> -----
> Endnotes:
> 
> [1] Instead of MOD(A1,1), you might want to write A1-INT(A1).  There are 
> defects in the Excel MOD function, at least in Excel 2003 and before.  I 
> don't recall if those defects would apply to MOD(A1,1).
> 
> [2] Instead of INT(A1)*5%, you might want to write ROUND(INT(A1)*5%,2).  It 
> should not be necessary when the tax rate is 5%.  But it could be needed for 
> other tax rates.
> 
> [3] http://www.maine.gov/revenue/salesuse/july2000charts.pdf
> 
> [4] http://www.maine.gov/revenue/salesuse/returnlink.htm
> 
> 
> ----- original message -----
> 
> "Bamforth" wrote:
> > First and foremost, I want to thank you for your time and effort. However, 
> > tax on $1.10 is just $.05 not $.06...Therein lies my problem. The tax 
> > schedule does not change above $1.00. The same calculations apply. But before 
> > I get carried away, I'm going to try your formula first. Once again, thank 
> > you very much.
> > 
> > "Joe User" wrote:
> > 
> > > "Herbert Seidenberg" <herbds7-msxls@yahoo.com> wrote:
> > > > Try $1.10
> > > 
> > > I did.  Try reading the assumptions in my posting. The OP failed to tell us 
> > > how sales tax is computed above $1.  So I wrote:  "Assuming tax of $0.06 an 
> > > amounts between $1.01 and $1.20".
> > > 
> > > And IF(A1<=0.1,0,ROUNDUP(A1*5%,2)) is indeed $0.06.
> > > 
> > > However, I have since looked up the Maine sales tax information [1].  I 
> > > learned that the tax table included by the OP applies only to amounts of 
> > > "less than" [sic] $1.
> > > 
> > > For amounts over $1, the rule is:  "Where the tax to be paid includes a 
> > > fraction of one cent, the fraction is not required to be paid where it is 
> > > less than one-half cent. A full cent, however, must be paid where the 
> > > fraction is one-half cent or more."
> > > 
> > > So it appears that the correct formula is:
> > > 
> > > =IF(A1<=0.1,0,IF(A1<=1,ROUNDUP(A1*5%,2),ROUND(A1*5%,2)))
> > > 
> > > ("<=1" matches the column title of the Maine tax tables.  And "<1" makes no 
> > > difference.)
> > > 
> > > So ironically, tax on $1.10 is indeed $0.06.
> > > 
> > > (But tax on $1.01 to $1.09 is $0.05.  New information, new results!)
> > > 
> > > 
> > > -----
> > > Endnotes
> > > 
> > > [1] http://www.maine.gov/revenue/salesuse/july2000charts.pdf 
0
Utf
4/22/2010 2:51:01 PM
"Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote:
> "This table shows tax to $100 by units of $1.00. The tax to
> be collected is the amount indicated below^ for each even
> dollar of the sale price plus the amount indicated above*
> for the remainder of the sale price."

Yes, I did overlook that straight-forward explanation, focusing instead on 
the paragraph above.

Thanks for pointing out my error in interpretation.

I provided a formula consistent with the above rule in another response to 
Bamforth just before I saw yours.


----- original message -----

"Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote in message 
news:0om0t5lsfg3sfrk5ehviuu3dptuecbsmj5@4ax.com...
> On Tue, 20 Apr 2010 23:53:45 -0700, "Joe User" <joeu2004> wrote:
>
>>"Herbert Seidenberg" <herbds7-msxls@yahoo.com> wrote:
>>> Try $1.10
>>
>>I did.  Try reading the assumptions in my posting. The OP failed to tell 
>>us
>>how sales tax is computed above $1.  So I wrote:  "Assuming tax of $0.06 
>>an
>>amounts between $1.01 and $1.20".
>>
>>And IF(A1<=0.1,0,ROUNDUP(A1*5%,2)) is indeed $0.06.
>>
>>However, I have since looked up the Maine sales tax information [1].  I
>>learned that the tax table included by the OP applies only to amounts of
>>"less than" [sic] $1.
>>
>>For amounts over $1, the rule is:  "Where the tax to be paid includes a
>>fraction of one cent, the fraction is not required to be paid where it is
>>less than one-half cent. A full cent, however, must be paid where the
>>fraction is one-half cent or more."
>>
>>So it appears that the correct formula is:
>>
>>=IF(A1<=0.1,0,IF(A1<=1,ROUNDUP(A1*5%,2),ROUND(A1*5%,2)))
>>
>>("<=1" matches the column title of the Maine tax tables.  And "<1" makes 
>>no
>>difference.)
>>
>>So ironically, tax on $1.10 is indeed $0.06.
>>
>>(But tax on $1.01 to $1.09 is $0.05.  New information, new results!)
>>
>>
>>-----
>>Endnotes
>>
>>[1] http://www.maine.gov/revenue/salesuse/july2000charts.pdf
>
> I think it's more complicated.
>
> I see a discrepancy between the written information and the table.
>
> The written rules seem to be a straight 5% with simple "arithmetic 
> rounding".
>
> Using the table, however, gives different results.  And it seems to me 
> that the
> fractional dollar table also applies to amounts over $1:
>
> "This table shows tax to $100 by units of $1.00. The tax to be collected 
> is the
> amount indicated below^ for each even dollar of the sale price plus the 
> amount
> indicated above* for the remainder of the sale price."
>
> ^below = the whole dollar table
> *above = the fractional dollar table.
>
> For example:  $1.25
>
> From the even dollar table: $0.05
> From the fractional table:  $0.02
>
> Your formula will give results inconsistent with the table for a number of
> values.
>
> At least, the whole dollar table seems to be straight 5%.
> --ron 

0
Joe
4/22/2010 2:52:44 PM
Joe,
I agree with the logic of your latest formulas,
but during tests I occasionally I get errors.
I tried this test formula:
=ROUNDUP(MOD(RANDBETWEEN(1000,4000)*2/100,1),2)
Occasionally the last digit is not even.
Herb
0
Herbert
4/23/2010 2:33:46 AM
"Herbert Seidenberg" <herbds7-msxls@yahoo.com> wrote:
> I agree with the logic of your latest formulas,
> but during tests I occasionally I get errors.

Good point!  I got sloppy.  I see that Ron's formula suffers from the same 
defect, albeit at different price points.

With my formula, MOD(16.10,1), for example, is not exactly 0.10.  This 
easily seen by formatting a cell containing that formula as Number with 15 
decimal places.  We see 0.100000000000001.

With Ron's formula, MOD(1.21,1), for example, is not exactly 0.21 (or 
greater).  Instead, it is exactly 
0.209999999999999,96447286321199499070644378662109375.  But this cannot be 
discerned when formatting to 15 decimal places, since Excel correctly 
formats that as 0.210000000000000.  Moreover, we might expect the VLOOKUP to 
work because =MOD(1.21,1)>=.21 returns TRUE.  But that is only TRUE because 
of Excel's heuristics, poorly explained in KB 78113 at 
http://support.microsoft.com/kb/78113.  It appears that VLOOKUP does an 
exact comparison.  We can simulate that in Excel; note that 
=MOD(1.21,1)-0.21>=0 returns FALSE.

Of course, the correction is to add some judicious rounding.  And at this 
point, I would defer to Ron's paradigm, although I prefer to use LOOKUP.

=ROUND(INT(A1)*5%
+ LOOKUP(ROUND(MOD(A1,1),2), {0,0.11,0.21,0.41,0.61,0.81},
{0,0.01,0.02,0.03,0.04,0.05}), 2)

ROUND(MOD()) is necessary.  I think the ROUND of the sum is prudent; it 
ensures that comparisons with hardcoded sales tax amounts match when 
expected.


----- original message -----

"Herbert Seidenberg" <herbds7-msxls@yahoo.com> wrote in message 
news:3a16df61-714b-441d-a88f-57f59e2faa89@g34g2000pro.googlegroups.com...
> Joe,
> I agree with the logic of your latest formulas,
> but during tests I occasionally I get errors.
> I tried this test formula:
> =ROUNDUP(MOD(RANDBETWEEN(1000,4000)*2/100,1),2)
> Occasionally the last digit is not even.
> Herb 

0
Joe
4/23/2010 8:36:42 AM
On Fri, 23 Apr 2010 01:36:42 -0700, "Joe User" <joeu2004> wrote:

>ROUND(MOD()) is necessary.  I think the ROUND of the sum is prudent; it 
>ensures that comparisons with hardcoded sales tax amounts match when 
>expected.

<slap on forehead>.  Thanks to you and Herbert for again pointing out the
issues with Excel Rounding.  We should be using it more.
--ron
0
Ron
4/23/2010 10:22:24 AM
Reply:

Similar Artilces:

Problem w/ installing CRM Sales for Outlook 1.2 and HTTPS
Hello, when trying to install CRM Sales for Outlook 1.2, the setup asks for a URL where my CRM server is located. Our CRM server is accessible only via HTTPS. Unfortunately, the CRM Sales for Outlook 1.2 setup cannot connect to its address. Is there a workaround? Best regards Christian. Hello. I have the same problem with configuring CRM Sales for Outlook. Anybody, an answer? "Christian Jacob" wrote: > Hello, > > when trying to install CRM Sales for Outlook 1.2, the setup asks for a URL > where my CRM server is located. Our CRM server is accessible only vi...

How to connect 2 sites using Exchange 5.5
I have 2 Exhanges runing in a diferent subnets. Yesterday everything was working fine, but now I can not send a email between sites. What can I do? ...

5.5 to 2003 migration
I want to run /forestprep and /domain prep in order for me to be ready to install the AD connector that would help me out with my migration. What are the cons of doing this?? is it safe. Do any have other steps in order for me to prepare for this migration? Thanks in Advance The Deployment Guide should help you out. http://www.microsoft.com/downloads/details.aspx?FamilyId=77B6D819-C7B3-42D1-8FBB-FE6339FFA1ED&displaylang=en -- Michael Barta [MSFT] Microsoft Exchange Support Please do not send e-mail directly to this alias. This alias is for newsgroup purposes only. This posti...

Apply view to all folders
Greetings, A user has numerous folders created under her Inbox in Outlook 2000. She woule like to create a custom view and then apply it to all folders automatically. I can create a custom view easily, but can't find a way to apply it to all folders other than on a one-by-one basis. Is there a way to apply the view to all folders at one time? Thanks much, Justin You can't apply it to all at once - unless you customize the Messages view. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Gu...

How do I apply a combo box to multiple cells in Excel so that it .
I would like to apply a combo drop down box in Excel. However I only want the box to appear when the cursor is over the cell can anyone offer me some help on this matter? Thank you, Dwain Hi Dwain, Perhaps you are looking for Data Validation, available on the Data menu. See Debra Dalgleish's tutorial at: http://www.contextures.com/xlDataVal01.html --- Regards, Norman "DB" <DB@discussions.microsoft.com> wrote in message news:6939ADC2-CF1C-46AA-B163-BEE27E23356A@microsoft.com... >I would like to apply a combo drop down box in Excel. However I only w...

view applied?
What does the following WLM beta message mean? View applied not connected to imap4.xxxxxx.co.uk I had already clicked on this account and a couple of emails were downloaded. ...

Task Dates in notification email are different than in the schedul
Has anyone ever seen the dates in a notification email for a task, showing different dates than in the schedule? I have a situation where the start date in the notification email (Feb. 15) is 7 days later for the task that is in the actual schedule (Feb. 8); the finish dates are the same (March 19)... Thanks. Phillip: It is possible for the planned start date on an assignment to be different than the planned start date of the task to which it belongs. So, what you describe is not likely a bug, but the result of changes made by the users in question. For example, a resour...

Average Sales per Store
Need help modifying the RMS "Average Sales per Store" report that is found in CustomerSource. Would like to add filters for Department and Category if possible. I have tried to modify it myself, but I'm a beginner at this and I keep coming up short. One other thing that may be useful is if it can summarize by date also - would be nice to see how the values change over the course of a week or so. "Jason Kelton" wrote: > Need help modifying the RMS "Average Sales per Store" report that is found in > CustomerSource. Would like to add filters for D...

Sorting by count and state
I have a large list of addresses, each address is really in a different format. I need to be able to sort the list by counties within states. Example of list: 12345 S. HArvey, Fort Hancock, TX 45678 PO box 14 Somewhere, MI 23454-345 I have a list of how zipcodes are done. The first 3 numbers indicate county. Does anyone have any suggestions, even an answer that might take some time would be helpful. My suggestion is a very labor intensive. Put each field into its own column. One row per record. Then you can sort it nicely. After you've gotten the zips in their own column, yo...

November 2004 Update Rollup for Exchange Server 5.5
Hello. Over the last few weeks my Exchange 5.5 SP4 server has been crashing mysteriously. The Information Store crashes and after I reboot the server everything is fine again. We have a mix of Outlook 2000 and Outlook 2003 clients. I noticed that there is a November 2004 Update Rollup for Exchange Server 5.5 which appears to address this issue. How can I tell if the network administrator before me applied this update to the server? Would it be listed under ADD/REMOVE PROGRAMS? I would really appreciate the help. Thanks. On Mon, 19 Dec 2005 11:51:51 -0500, "Corbin O'Reilly&q...

I've applied a Fill and now can't see the gridlines....
Does anyone know how to show the Gridlines through a fill? I've searched and searched but can't find the answer, yet the silly thing is I know it can be done as I have done it before (by accident) Any help mostgratefully received! bestregards, Tim Apply borders... In article <62E1EA83-287A-4FE3-BEC6-7C0155EA1850@microsoft.com>, "FizzyBunghole" <FizzyBunghole@discussions.microsoft.com> wrote: > Does anyone know how to show the Gridlines through a fill? I've searched and > searched but can't find the answer, yet the silly thing is I know it c...

Comparison b/w Exchange 5.5 and 2003
Hi all, Any document that has the features comparison b/w Exchange 5.5 and 2003? Basically, I want to push our Management to upgrade to Exchange 2003 (Clustered preferably). TIA. ,Sur Here you go: http://www.microsoft.com/exchange/evaluation/features/ex_compare.mspx Not many blue dots in the 5.5 column. :) -- Neil Hobson Exchange MVP For Exchange news, links, and tips, check: http://www.msexchangeblog.com "Sur" <nospam@nospam.com> wrote in message news:e0Rq$Y9HFHA.904@tk2msftngp13.phx.gbl... > Hi all, > > Any document that has the features comparison b/w...

M2005 Cannot Apply Epayment To Invoice
This is a multi-part message in MIME format. ------=_NextPart_000_000C_01C4AD73.81F30C10 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I create invoices for payments from customers. I have those = automatically charged to their bank accounts and credited to my bank = account. When the payments hit my bank account and are downloaded into = Money, I go into the transaction and enter "Payment For Invoice" and = then try to go to the apply payment to an invoice screen but a nice = little pop up comes up in Money 2005 that says &qu...

Move/Migrate 7.5 To New machine
I'm a sysadmin spearheading the migration/move of a GP 7.5 installation to a new Win2K server (SQL Server 2000, of course). Right now we do not have a GP consultant to help us with this. I would like some tips/experiences of others who have done this. Do you really need a consultant to help. (And if you are a GP consultant, please tell me why we need you!) We also have two allied apps to move: Xpede and WorkforceROI. Any experiences you might having moving/replicating these apps along with GP would be helpful. Many thanks! Rich I just did this migration a week ago. First, there is a ...

Documents w/o namespaces
I've developed a web service whose output is an XML document that conforms to a schema. Is it bad practice / unacceptable not to have a namespace associated with my document? If so, is it OK to just supply a default namespace and thus negate the need for namespace prefixes on all elements? Lastly, is it OK to reference the URL of the .xsd schema document itself as the URI of the namespace? Brad Wood wrote: > I've developed a web service whose output is an XML document that > conforms to a schema. > > Is it bad practice / unacceptable not to have a namespace ass...

HTML mail sent by exchange 5.5 server
I try to send an HTML mail with some jpg images inside If I send to internal user, all work fine If I send to internet user, they receive the message in wrong formatting, and instead of viewing the images they receive a <<...OLE_Obj...>> for each image I have already enabled HTML in outbound attachment in Internet Mail Connecctor Someone can help me ??? Is this true with all external clients? On Sat, 20 Nov 2004 03:33:02 -0800, "Stefano" <Stefano@discussions.microsoft.com> wrote: >I try to send an HTML mail with some jpg images inside > >If I s...

Exchange 5.5 to Exchange Server 2003
I've looked through quite a few posts here, read a lot of things and still I am not 100% of the best way to go about this scenario. One EX 5.5 server at SP3 on W2K SP3, single AD domain in the forest. Whats the best method to get from this to Exchange Server 2003 on Windows Server 2003? Is is to install Exchange Server 2003 on the Windows Server 2003 box, create a new Exchange Server 2003 organization and then use the Migration Wizard? Or is it soemthing else? TIA I would recommend this procedure, http://www.msexchange.org/tutorials/Migrating_Exchange_5_5_to_2003.html -- John Ol...

Back Office 4.5 & Exchange 5.5 Server migration to Win 2003 & Exchange 2003 Server
Hello, Is it possible to migrate a Back Office & Exchange 5.5 server to Win2k3 & Exchange 2003 and keep our NT 4.0 Domain enviroment. ==================== This is what we have: Winnt 4.0 PDC Winnt 4.0 BDC Back Office 4.5 w/ Exchange 5.5 (we want to eliminate this machine!!!) ===================== We have a seperate Win2k3 machine ready but not joined to the domain yet. Do we have to upgrade our Winnt 4.0 PDC aswell to Win2k3 or will our new Win2k3 w/ Exchange 2k3 fit in well in the NT 4.0 domain? What options & steps would be best to migrate? Thanks ahead of time, -Rene Enriq...

NT4 and exchange 5.5 running on AD Native mode ?
we want to switch to native mode on AD is there a problem if you have still some exchange 5.5 running on NT4 and other servers on exchange 2003 running on windows 2003. then switching to native Active directory? thanks is nayone doing it? ...

Cross Apply problem
How do I get the desired result for the following: SOURCE TABLE: USSoilLyr FIELDS: TAXOR PARTSIZE PCT_R Alfisols ashy 60 Alfisols ashy 57 Alfisols ashy 38 Alfisols clayey 85 Alfisols clayey 79 Andisols medial 50 Andisols medial 36 DESIRED RESULT (TARGET TABLE): T_Order FIELDS: TAXOR PARTSIZE PCT_R Alfisols ashy AVG(60+57+38) Alfisols clayey Avg(85+79) Andisols medial Avg(50+36) My T-SQL Code, as follows, returns wrong calculations when I manually check the results. UPDATE T_Order SET ...

Outlook XP delays contacting Exchange 5.5
I have three clients on a 100 node network that are having issues while in Outlook Xp connecting to our Exchange 5.5 SP4 server. A message pops up in Outlook XP sporadically that says "Waiting to contact Exchange server" and this hangs up the system for sometimes up to 60 seconds until it completes. This happens while sending, receiving or basically just having Outlook open on the desktop Any idea's as to where to fix this problem? I make the assumption it is at the desktop level with Outlook XP, and not at the server side of things since 99% of our email clients do not experie...

Install V10 HR after applying service pack 3
I am currently on V10, SP3 and now have purchased the HR module. I am not sure of the steps I need to take to load the HR module (we have the V10 DVD that is not sp3) Are these my steps? 1. Load the HR module from V10 DVD on SQL Server install (I was told previously to use the Add/Remove programs option and change but it still asks for the DVD) 2. Do I need to reload SP3 on server install? 3. Then load the HR module (from V10 dvd) on additional workstations? If I have to re run the SP3 I am concerned it is going to take hours to complete like original instal of SP3 If anyone has a...

Apply Multicurrency Invoices in Bank Management
I oppened a ticket to know why I cant apply Multicurrency Invoices to payment transactions in Bank Managment as it is available in Payable Management. I've been suggested to post a suggestion for that. Simply, while doing a payment in Bank Management. If the Chequebook Currency is diffrenet than the invoices currencies that you are paying againist, then you can't apply these invoices. Thus, you cant see them in the remittance advice. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestio...

Public Folder Migration #5
I want to migrate my Public Folders to a new Exchange server mail server. I have migrated my mailboxes using ExMerge, however, I have no idea on how to migrate my Public Folders. Please help me. Thanks, S Add Your new exchange server also to public folder replication server list. Once it get replicated you can remove old exchange server. And all public folder will be by default Mail enabled public folders when you migrate. Regards, Mallika. "Skc" wrote: > I want to migrate my Public Folders to a new Exchange server mail server. > > I have migrated my mailboxes us...

DPM 2010 Workstation System State Backup
Hello everyone, i recently upgraded to DPM 2010 RC from DPM 2007 SP1 and am having an issue backing up the system state on both 32 and 64 bit windows xp workstations. More specifically it wont let me select the systemstate (it's not there) when building the protection group. This was not an issue on DPM 2007 SP1. The version of the protection agent on each workstation is 3.0.7558 .. As an additional note all 03 and 08 server system states back up with out issue. Thank you, -Dom Workstation SystemState backup is deprecated in DPM 2010. Just to work-aroun...