Multiple IF functions

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to create an IF function to calculate some payroll taxes. Here's
the formula that I came up wit
=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?
Thanks!
Terri
 
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?
Click to expand...


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
Click to expand...

--ron
 
IF(SUM(B9:K9)>8000,0,((SUM(B9:K9)-8000)*-$B$72))
i think your problem is this....
you have already tested for < 8000(what happens if = 8000),so
IF(SUM(B9:K9)>8000 is false ,it will evaluate ,((SUM(B9:K9)-8000)*-$B$72))
and stop.Your if statements need to be logical and keep going forward like
the first two ifs...
 
Back
Top