#### 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. 0 followers.

16 Replies
421 Views

Similar Articles

[PageSpeed] 54

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

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

Similar Artilces:

Send as group in Exchange 5.5
I have a customer that had groups set up where the "From" field shows the group address when one of the members sends an e-mail. For example, they have a group of users called "Exec Admin." When one of the members sends a message, the message displays that it's from the "Exec Admin" group. Can anyone tell me how this is set up? The administrator made a change to one of the users and this no longer works for the whole group. I need to go and help them troubleshoot, but I'm not even sure how this is configured. Thanks! Give them Send As permissi...

Word found no merge fields in your main document.
I have been having good luck with mail merge but have suddenly encountered problem merging Excel data into a word document. All goes well until I elect Entire worksheet. It is at this point, an error? window opens with this message: Word found no merge fields in your main document. Choose the edit main Document butto to insert merge fields into your main document As this may be more a Word problem, I will post in both areas. Here is additional information. I have Office 2000 and XP. Recently working with this same database I created a phone directory. It turns out the data was old and I have...

Service Scheduling
We have recently installed CRM in our organisation and I am slowly going through customising each each section to suit our business. I have an issue with Service Activity, whereby when you go in and click schedule it goes through and allows you to select your resource/site details, and then when you click find resource it cannot run scheduling engine. Same issue occurs if you go in and try and create a task/appointment. It appears to be something with Exchange and CRM. However email tracking works and the exchange email router works fine. Just wondering if any body else has experieced this i...

Information Store properties on 5.5
I've migrating and can't seem to find where in Exchagne Admin to change the properties as described in the Ed Crowley Method. See: <http://www.swinc.com/resources/exchange/faq_db.asp?status=questions&faqI D=1000&faqname=Exchange%205.5&sectionID=1008&sectionName=The%20Ed%20Crowl ey%20Server%20Move%20Method> Could someone point me to the right place? Thanks. Charles Which properties would those be? -- Ed Crowley MVP - Exchange "Protecting the world from PSTs and brick backups!" "Charles Woolever" <info@existingstations.com> wrote in ...

Exchange 5.5 w/ bad block on hard drive
My priv.edb file is on a drive that has a bad block. Can run a scan disk on this drive while exchange is still running? Also, if I right click on the disk with the bad block in the disk management console, it gives me the option to reactive the drive. How The drive is working. Do I still need to reactive it? Thanks, Ben ...

FW: Apply that correction patch from the M\$ Corporation
--elaqtfeugimiwd Content-Type: multipart/related; boundary="wrpjyfjhzqe"; type="multipart/alternative" --wrpjyfjhzqe Content-Type: multipart/alternative; boundary="bhkmumoweqwyynujv" --bhkmumoweqwyynujv Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Microsoft Customer this is the latest version of security update, the "September 2003, Cumulative Patch" update which fixes all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three new vulnerabilities. Install now to conti...

Taxes with Landed Costs
Is there a reason I am unable to add taxes specifically to a landed cost? A setting somewhere? Using advanced tax calculations. tia ...

Money 2006
It will soon be here people, I bet everyones excited: http://www.amazon.com/exec/obidos/tg/detail/-/B0009KLGPO/qid=1119388434/sr=1-1/ref=sr_1_1_etk-software/103-5923059-1311820?v=glance&s=software&n=229534 http://news.com.com/Microsofts+Money+2006+available+for+preorder/2100-1012_3-5756135.html?tag=nefd.top :-) -- Andre Extended64 | http://www.extended64.com Blog | http://www.extended64.com/blogs/andre http://spaces.msn.com/members/adacosta FAQ for MS AntiSpy http://www.geocities.com/marfer_mvp/FAQ_MSantispy.htm It hurts my head just thinking about it. "Andre Da Costa [Ext...

Apply To All
i would like to run a macro so that my sheet will open at 97% zoom. can do it for an individual sheet upon open with activewindow.zoom 97, but i would like to know how this page setup can be applied to ol excel workbooks. If it is possible it would be best that upon openin any workbook, the workbook would set up as 97% zoom. The primary focu is on the old workbooks changing over, is there something that can b set up in the personal workbook that would do this? thank -- chie ----------------------------------------------------------------------- chief's Profile: http://www.excelforum.c...

Same report but the total amount wrongly showing as zero for different GP users
Could you please advise me why the total (sum-up) showing zero amount for different logon GP user. http://img22.imageshack.us/img22/9043/custinqrptchris.jpg http://img22.imageshack.us/img22/6799/custinqrptsf.jpg It is the same report, same company, same report criteria, but different is only the GP logon user. One of the GP user can get the total amount incorrectly showing as ZERO balances in the report, but if another GP user logon and print the same report, the total amount is correct. Why is that so? Please advise, and many thanks in advance. This could happen if differe...

Exchange Server 5.5 Post-Servicepack 4 Internet Mail Service Fixes
Hello, I am looking for the Post-Servicepack 4 Internet Mail Service Fixes for Exchange Server 5.5 German, which cannot be ordered free of charge through Microsoft support. The following fixes are needed for the german version: 301690 XIMS: Corrupted Message Header Causes Access Violation in Msexcimc.exe 272672 XIMS: Internet Mail Service Does Not Generate Non-Delivery Report Message If Number of Recipients Exceeds 32,766 279320 XFOR: Messages in the Internet Mail Service Pickup Folder May Be Moved to the Bad Mail Folder 285023 XCON: Internet Mail Service Does Not Follow Time to Live for ...

Another O/T (Sorry)
Can you upgrade Windows 2003 Standard to Windows 2003 Enterprise simply by inserting the CD and running it? Does it make a difference to applications already installed (such as Exchange 2003 Enterprise on a Windows 2003 Standard server?) TIA Exchange doesn't really mind. Windows 2003 Enterprise allows more hardware options, like cluster sizes, RAM, etc. Windows 2003 Standard to Windows 2003 Enterprise is a supported upgrade path. This question is best asked in the Windows Server newsgroup. Hades wrote: > Can you upgrade Windows 2003 Standard to Windows 2003 Enterprise simp...

Query by receivables payment to show how applied
I'd like to construct a query to show how receivables payments were applied. Basically it needs to do the same thing as the Apply to Debits window (which doesn't have a print option). Since payments could be in either the open or history tables, how can I query against both in a single query? Thanks. -- Jim@TurboChef ------=_NextPart_0001_137F2AE4 Content-Type: text/plain Content-Transfer-Encoding: 7bit Hello Jim, I understand you want a report that is equivalent to the Applied to Debit window. Unfortunately, there is not currently a report or SmartList that has this infor...

amortization schedule
What product of microsoft money includes an amortization schedule. Is it either Premium 2005, Delux 2005, or Standard 2005. Deluxe, at least, probably Standard, as well. Before you go to the trouble of installing it, ask a more detailed question. You may be assuming a lot. Nobody can provide more detailed answers unless you ask them. -- Chris Cowles Gainesville, FL "lanemt" <lanemt@discussions.microsoft.com> wrote in message news:376759DD-1ECC-458E-AA57-05641CF3AA04@microsoft.com... > What product of microsoft money includes an amortization schedule. Is it > eit...

How do I print a SINGLE POSTCARD on 5.5x4.25in stock?
I have a preprinted postcard that is 5.5" x 4.25". A standard postcard. I cannot for the life of me to get Publisher to print on this cardstock!! It keeps thinking the page is "LETTER 8.5x11". I have NO problems getting 4 cards (multiple addresses) to print on one page. But now I have the cards pre-printed and just want to mail-merge an address list onto them. How do I set this up? Because apparently, using the post card template of 5.5x4.25 (or even the 6x4) doesn't do it!!!!! Thanks for any insight. Jeff Jeff, File, PRINT SETUP Here select your stock si...

RM Apply Sales Search Options
When applying payments in this screen, we like to search invoices by invoice dates / due dates, or sort the invoices to be paid by Due Date. But these options are not available. How can you sort the grid by Due Dates? Thanks! Arman, You could perhaps create a Dexterity customization that will add a drop-down with the search criteria and a corresponding field for each search option selected from the drop-down. Based on the search option selected you can use the Dexterity "fill window from current" statement to redisplay the records. I think you could perhaps do the same w...

Counting in Excel #5
Got it! Thanks to all of you for your help -- Angel16 ----------------------------------------------------------------------- Angel160's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1550 View this thread: http://www.excelforum.com/showthread.php?threadid=27561 glad to help -- Don Guillett SalesAid Software donaldb@281.com "Angel160" <Angel160.1f98km@excelforum-nospam.com> wrote in message news:Angel160.1f98km@excelforum-nospam.com... > > Got it! Thanks to all of you for your help. > > > -- > Angel160 > ---------------...

Hotfix 837794 Exch 5.5
I have seen that a couple of people have been looking for this hotfix. Has anyone found it? I noted that some of the resposes were to get it from MS. I tried several times. They all said it was an obsolete program and this hotfix was no longer available. The last guy I spoke to suggested I post here to see if I could get it. I need it for my 5.5 server to stop sending NDRs as MessageLabs is now blocking us. I know there are other programs for purchse that will help in this regard, but I am hoping that this will just require a temporary fix and I will be able to upgrade the whole system ...

Apply the old index to include new chpaters.
Our book was INDEXED USING WORD 2003 INDEX. Now we have added 5 NEW CHAPTERS. Is there a way to apply the words that have been indexed to include occurrences in the new chapters? Does word 2007 offer new indexing features that I can help in this? On Wed, 16 Dec 2009 13:46:01 -0800, Baha <Baha@discussions.microsoft.com> wrote: >Our book was INDEXED USING WORD 2003 INDEX. Now we have added 5 NEW CHAPTERS. >Is there a way to apply the words that have been indexed to include >occurrences in the new chapters? > >Does word 2007 offer new indexing features ...

Exchange 5.5 and 2003 #3
Greetings. Our company uses Exchange 5.5 as organization and some filials have Ex2003 as main mail server. Couple of days ago users at Ex2003 stop recieving mail form 5.5 users. They're recieving NDR that user IMCEAEX_-o... is unknown. I've telneted to Ex2003 and try send mail to user@company.com it's ok. But if I try to send mail to IMCEAEX-_o=Organization_ou=COMPANY... there is no mail. I thought that it might be problem with DC connection or ADC links, but it's ok. DCDIAG pass all test, and there are no Errors in log on server with srs. Can some one help? I had the same pr...

Scheduling Campaign Emails #2
In CRM 4, is there a way to schedule when campaign emails go out? We need to send out about 20,000 emails, and we would like those to be spread out over a decent time period. Thanks, Tony ...

Traffic Hurricane Source Code Sale-This Friday!
Someone over at TrafficHurricane.com has definitely lost his marbles- I just found out that they are selling off the actual source code and Master PL rights to this hot selling software starting this Friday. I know this is hard to believe but I swear its true. You can get a sneak peek by going to: http://traffichurricanesourcecode.com/go/83 The reason I am letting you know about a sale that does not even start until Friday is because I wanted to make sure you wait for my email before you pick it up! I am going to make it *very* worth your while to wait and see the special bonuses I am go...

How to email directly from publisher a document in the main body .
I am tring to email a flyer that I constructed in publisher. I do not want to send it as an attachement What version of Windows, Publisher, Outlook and Outlook Express are you using? -- Brian Kvalheim Microsoft Publisher MVP http://www.publishermvps.com This posting is provided "AS IS" with no warranties, and confers no rights. "boxtteri" <boxtteri@discussions.microsoft.com> wrote in message news:E25049D9-676C-4ACD-8B6E-DFD83CB04267@microsoft.com... >I am tring to email a flyer that I constructed in publisher. I do not want >to > send it as an attache...

Missing main body of message in Outlook 2003
In Outlook 2003 main body of received text message is missing for some emails but okay for others. I can see the received email header and attachements but not the message itself. When I look at the same email in MSN it appears complete. Any help would be much appreciated. Thanks Tried it already with SP1 installed? Check Office Update to install it. -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -Navigation Pane Tips & Tricks -Create an Office 2003 CD slipstreamed with Service Pack 1 ----- "Yusuf" <Yusuf@discussions.microsoft.com> wrote i...

Rule apply : sender does not have an e-mail
Hello everyone, In the CRM Workflow Manager, i created a manual rule who send an e-mail to a contact. The owner of the rule is Joseph administrator (administrator@crmdomain.com) I cant choose the address e-mail "from" so i suppose it's administrator's email. When i apply this rule to a contact, an error occurs "E-mail cannot be sent because the specified sender does not have an e-mail address. Add an e-mail address to the send and then try again." My sender is Joseph administrator, isn't he? so why he does not have an e-mail address? What can i do...