Bamforth said:
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 said:
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 said:
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!)
