=if(and( Errors

[jhorness]

This is the formula I have so far...

=ROUND(IF(B4="",0,IF(AND(F4>=22,F4<49),1,IF(AND(F4>=49,F4<77),2,IF(AND(F4>=77,F4<105),3,IF(AND(F4>=105,F4<133),4,IF(AND(F4>=133,F4<161),5,F4/28)))))),0)

The formula I have above works fine, except when I try to add another =if(and( statement, I get an error message because I think I have too many And statements. I need to add 7 more statement for the remainder of the months in a year, 6 - 12.

Any idea on how I can fix this? Thanks

 

[Jonmo1]

A common method to replace this structure...
IF(AND(F4>=22,F4<49),1,IF(AND(F4>=49,F4<77),2,IF(AND(F4>=77,F4<105),3,IF(AND(F4>=105,F4<133),4,IF(AND(F4>=133,F4<161),5

is
LOOKUP(F4,{0,22,49,77,105,133,161},{0,1,2,3,4,5,0})

assuming numbers Less than 22 or Greater than 160 should return a 0

Hope that helps..

 

[gnaga]

I think you can nest only 7 IFs more than taht you may not be able to add. Check with Help

TIA
GNaga