building a health tax calculator

[Guest]

Formulas : building an excel calculato

I want to build a health tax calculator in Excel. I Have 5 salary ranges with a different tax percent for each range. As the number of householders increases over 2, 1/4 of 1 percent will be deducted for each person up to 6 people. I am using Windows 2000, and Excel from Office 2000. I would appreciate any advice, book suggestions, etc.
ceil
(e-mail address removed)

 

[Bufo Calvin]

Is there a column with a number in it for the "number of
househoulders"? If not, how do you know who goes with
which household?

-----Original Message-----
Formulas : building an excel calculator

I want to build a health tax calculator in Excel. I Have

5 salary ranges with a different tax percent for each
range. As the number of householders increases over 2,
1/4 of 1 percent will be deducted for each person up to 6
people. I am using Windows 2000, and Excel from Office
2000. I would appreciate any advice, book suggestions,
etc.

 

[Sandy Mann]

There may be a more elegant solution along in a moment - there usually is.

With the total income in A2 and the number in the house in B2,

=A2*(((INT(A2/5000)-1)-((B2-2)-(B2>6)-(B2>7))*0.25)/100)

seems to give the right answers with the limited testing that I have carried
out

HTH

Sandy

--
to e-mail direct replace @mailintor.com with @tiscali.co.uk

In reading over post I realized I wasn't too clear. Here are the parameters

Five household income ranges with their corresponding tax:

$15,000-19,999 - tax is 2%; 20,000-24,999 - tax is 3%; 25,000-29,999 - tax
is 4%; 30,000-34,999 - tax is 5%; 35,000-39,999 - tax is 6%

Household income consists of total of 1 or more members salaries.

Household members are not taxed individually but as a group

Different tax for each income range. That tax applies to households up to

2 persons, and then for persons 3, 4, 5 and 6 of that household 1/4 of 1
percent is deducted for each.

So if you have a household of 8 people, 4 of them working with a combined

household income of $33,000, the tax would be 5%. For persons, 3, 4, 5, and
6, 1/4 of one percent would be deducted for each so the tax would be 4%
instead of 5% for this household.

The number in the household is important but how few or how many are

actually working is not part of the equation. Whether its one person
working or all 8, its the total of their salaries that determines the
household income range.