Excel If function with the and condition

Suzanne Cadden 0
Viewed 18

I am trying to calculate some payroll taxes based on the state certain employees are in and I am getting results that I shouldn't be based on a fairly simple IF AND statement. As you can see in columns N - Qif have the same formula except I have updated the state to match and the columns it is pulling from. On row 8, I should only be getting a result in column N but I am getting a result in all four columns. I have not pulled the formula down because I know there is an error. Can someone help?

My formula:

=IF(AND($B8="CA",$K8<$O$3),$K8*$O$4,$O$3*$O$4)

column B = State, K = Gross Salary, row 3 is the wage threshold for that tax, and row 4 is the tax % to calculate. The formula in this cell indicates that B8 should equal CA but on this particular row, the data in that field is WA and yet I am getting a result in that CA column.

enter image description here

conditional-statements if-statement excel
1 Answers

When you copy and paste your formula across multiple cells the absolutes $ stay focused on those cells and don't change even if your in a new column. You need to remove some of the absolutes $ if you want the formula to adjust to another column.

It appears that you have many states so I would use if the row 2 (State) SUI Tax matches what is in Column B. You should also test just your AND() by itself to make sure it is working right.

=AND(ISNUMBER(FIND($B5,O$2)),$K5<O$3)

After texting that finish the IF(). It is not clear to me what you want returned for the IF() True and False. If you want more help post a comment if you are satisficed with the answer check the checkmark.

Shane S0
Related