IF formula not working properly

**argentraven** · 06-04-2013, 01:37 PM

I've looked at this too long and cannot see why it's broken. Maybe someone out there can see what I'm missing.

The formula below needs to look at a number in the previous cell (that number will be between 0 and 38) and then return a value based on that number.

=IF(A12<=2,0,IF(A12<=5,1,IF(A12<=8,2,IF(A12<=11,3,IF(A12<=14,4,IF(A12<=17,5,IF(A12<=20,6,IF(A12<=23,7,IF(A12<=26,8,IF(A12<=29,9,IF(A12<=32,10,IF(A12<=35,11,IF(A12<=38,12)))))))))))))

The formula seems to break at the IF bolded in the formula above.

Here are the perameters that the formula is based on;

0 thru 2 returns 0
3 thru 5 returns 1
5 thru 8 returns 2
9 thru 11 returns 3
12 thru 14 returns 4
15 thru 17 returns 5
18 thru 20 returns 6
21 thru 23 returns 7
24 thru 26 returns 8
27 thru 29 returns 9
30 thru 32 returns 10
33 thru 35 returns 11
36 thru 38 returns 12

**Jonmo1** · 06-04-2013, 01:43 PM

Excel 2003 is limited to 7 Nested If's.
Try this

=MATCH(A12,{0,3,5,9,12,15,18,21,24,27,30,33,36},1)-1

**ConneXionLost** · 06-04-2013, 01:43 PM

Try this instead:

Please Login or Register  to view this content.

**argentraven** · 06-04-2013, 01:43 PM

AHA! Brilliant, thank you! Both of those worked!

**ConneXionLost** · 06-04-2013, 01:45 PM

The point is, if you need to nest more than seven IFs, then you should use a different approach.

**argentraven** · 06-04-2013, 01:53 PM

Of course after the fact is when I understood the need for a different approach. Thank goodness there are people like you and Jonmo1 to point others in the right direction. Again, thank you both for the quick responses. I would have been pounding my head on my desk if not for you two.