Multiple IF functions
On Sat, 29 Oct 2005 16:08:02 -0700, "questionsforms"
wrote:
I'm trying to create an IF function to calculate some payroll taxes. Here's
the formula that I came up with
=IF(AND(L9<8000,SUM(B9:L9)<8000),L9*$B$72,IF(SUM( B9:K9)8000,0,((SUM(B9:K9)-8000)*-$B$72))+IF(AND(L18<8000,SUM(B18:L18)<8000),L18*$B$ 72,IF(SUM(B18:K18)8000,0,((SUM(B18:K18))-8000)*-$B$72 ETC. it's long formula
It works properly until one of the if statements in a section (
IF(SUM(B9:K9)8000,0,((SUM(B9:K9)-8000)*-$B$72)) ) come up with the answer 0
then the formula stops and doesn't continue to the next section.
What am I doing wrong?
Using an IF statement.
It will be much clearer to set up a table. Taxes can usually be represented by
a table showing the tax (in currency) on the amount up to the Gross Pay in the
leftmost column + a percent to be applied to the amount over that value (but
less than the value in the next row).
GrossPay TaxAmt TaxPercent on amount over that in Gross Pay Col
0 0 5%
5000 a 10%
15000 b 12%
etc.
a = tax on first 5,000
b = tax on first 15,000
Then use VLOOKUP to calculate the tax.
=vlookup(salary,table,2) +
(salary-vlookup(salary,table,1)) * vlookup(salary,table,3)
Thanks!
Terri
--ron
|