Help with IF statement

**jarlag** · 02-17-2012, 04:22 PM

I am trying to use an IF statement to calculate payroll taxes based on what state someone lives. I tried using a nested if statement and keep getting errors. I am only checking for 2 states and depending on the state, the calculation is different because of changes in state unemployment tax rates. At the same time, I am also checking in each state what salary level a person is at in a year since some taxes max out at a certain salary level, so the rate changes for a person as they max out in one of the taxes.

So for example, first I check for the state and if it is "KY", then I check if the salary so far exceeds $110,100, if it does, then I apply 1.45% to their salary for payroll tax, if it does not exceed and exceeds $9,000, then I apply 7.65%, if it does not exceed $9,000 but exceeds $7,000, then I apply 10.35%, otherwise I apply 10.95%. Then I need to continue because now if "KY" is not true from the beginning, I assume it is the second state and do the same thing as before just using different rates. This is the formula I tried and got errors. ANY SUGGESTIONS would be appreciated

=IF(H147="KY",IF(W19>110100,.0145*W19,IF(w19>9000,.0765*W19,IF(W19>7000,.1035*W19,.1095*W19)))),IF(W19>110100,.0145*W19,IF(W19>9000,.0765*W19,IF(W19>7000,.11*W19,.115*W19)))

**NBVC** · 02-17-2012, 04:33 PM

Maybe you can create a table that lists the state abbreviations across the top row, and the lower bound of each salary range down the left column, and fill in the approprate percentages in the matrix, then you can use an INDEX/MATCH function.

e.g.

=INDEX('Sheet2'!$D$1:$Z$10,MATCH(W19,'Sheet2'!$D$1:$D$10),MATCH(H147,'Sheet2'!$D$1:$Z$1,0))*W19

where 'Sheet2'!D1:Z10 contains the table.. you want to lookup the salary from W19 in D1:D10 of the table, and lookup the state from H147 in D1:Z1 of the table.

Note the first MATCH() function, doesn't have the 3rd option 0 argument, because it is not looking for exact value, but closest without going over...