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

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

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

=ROUND((Amt+0.1*(MOD(Amt,1)>0.2))*0.0499,2)

"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

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

Try $1.10

"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 

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 
> 
> .
> 

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

"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 

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

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 

"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 

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

"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 

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