Nested IF AND Statement

[Kelly O]

Good Afternoon,

I am using a nested IF AND statement to look at a salary, look at a range and then use the percentage associated with that range (hope that makes sense)

If I type the formula like this
=IF(AND($C17>=$n$2,$C17<=$o$2),$C17*$m$2,IF(AND($C17>=$n$3,$C17<=$o$3),$C17*$m$3,IF(AND($C17>=$n$4,$C17<=$o$4),$C17*$m$4,if(AND($c17>=$N$5,$c17<=$O$5,$c17*$M$5,if($c17>=$N$6,$c17*$M$6,"")))))
Excel tells me that there is an error

If I type it like this
=IF(AND($C17>=$n$2,$C17<=$o$2),$C17*$m$2,IF(AND($C17>=$n$3,$C17<=$o$3),$C17*$m$3,IF(AND($C17>=$n$4,$C17<=$o$4),$C17*$m$4,if(AND($c17>=$N$5,$c17<=$O$5,$c17*$M$5,if($c17>=$N$6,$c17*$M$6,""
Excel tells me my formula is missing a parenthesis. I have been staring at this for an hour - any help would be greatly appreciated

I have used the above formula in an earlier part of my sheet and it has 1 less AND statement and worked perfectly. Am I used too many AND Statements?

 

[Special-K99]

You dont need the ANDs. If N2:N6 is in ascending order then just use an IF on the highest value first, ie
=IF(C17>N6,M6,IF(C17>N5,M5,IF(C17>N4,M4,IF(C17>N3,M3,IF(C17>N2,M2,0)))))*C17

Or replace the entire formula with this

=INDEX(M$2:M$6,LOOKUP(C17,N$2:N$6))*C17

This assumes C17 will be >= to at least one of the salaries otherwise

=IF(C17>N2,INDEX(M$2:M$6,LOOKUP(C17,N$2:N$6))*C17,"")