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:

=(A10.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:
=(A10.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:

=(A10.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" 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/salesus...2000charts.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" 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/salesus...2000charts.pdf

.

On Tue, 20 Apr 2010 15:11:02 -0700, B Lynn B
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/salesus...2000charts.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" 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/salesus...2000charts.pdf

On Tue, 20 Apr 2010 23:53:45 -0700, "Joe User" <joeu2004 wrote:

"Herbert Seidenberg" 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/salesus...2000charts.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/salesus...2000charts.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" 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/salesus...2000charts.pdf

"Ron Rosenfeld" 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" wrote in message
...
On Tue, 20 Apr 2010 23:53:45 -0700, "Joe User" <joeu2004 wrote:

"Herbert Seidenberg" 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/salesus...2000charts.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" 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,96447286321199499070644378662109 375. 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" 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

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