Need help with this formula

[amunrob]

I'm using a nested If/And formula with 13 different logical tests. Basically, I'm trying to get an age value displayed when I enter a date of birth into cell B3. Of the 13 logical tests, I have 3 that span a number of years and then the other 10 are only covering one year. When I input the formula, the 3 that span multiple years works just fine, but the other 10 show VALUE### as the output. When I put in the formula for just one of the 10 without the rest of the function, it also works well, just not within the rest of the nested ifs. I'm going crazy trying to figure this out and need some help if anyone can oblige.

The formula I am using is this:

=IF(AND(B3>="01/01/1940"+ 0, B3<="12/31/1947"+0), "55", IF(AND(B3>="01/01/1948"+0, B3<="12/31/1948"+0), "55, 2 Months", IF(AND(B3>="01/01/1949"+0, B3<="12/31/1949"+0),"55, 4 Months", IF(AND(B3>="01/01/1950"+0, B3<="12/31/1950"+0), "55, 6 Months", IF(AND(B3>="01/01/1951"+0, B3<="12/31/1951"+0), "55, 8 Months", IF(AND(B3>="01/01/1952"+0, B3<="12/31/1952"+0), "55, 10 Months", IF(AND(B3>="01/01/1953"+0, B3<="12/31/1964"+0), "56", 0))))))) + IF(AND(B3>="01/01/1965"+0, B3<="12/31/1965"+0), "56, 2 Months", IF(AND(B3>="01/01/1966"+0, B3<="12/31/1966"+0), "56, 4 Months", IF(AND(B3>="01/01/1967"+0, B3<="12/31/1967"+0), "56, 6 Months", IF(AND(B3>="01/01/1968"+0, B3<="12/31/1968"+0), "56, 8 Months", IF(AND(B3>="01/01/1969"+0, B3<="12/31/1969"+0), "56, 10 Months", IF(AND(B3>="01/01/1970"+0, B3<="01/01/2000"+0), "57"))))))


Thanks in advance.

 

[Dave Patton]

Try a lookup formula

=LOOKUP(B3,rValues)

rValues is a named range of dates in first column and required text in second column.

 

[Dave Patton]

You could use Excel's Formula Evaluate to isolate the problem with your formula.

I looked at the first IF and on my system I had to edit the second date,

The minor edit then yields the correct result

=IF(AND(B3>="01/01/1940"+ 0, B3<="31/12/1947"+0), "55","")


Did you try using a Lookup formula?