Trouble with nested IF equation

[richardthelionheart]

=IF(Z700=1, "st", IF(Z700=2, "nd", IF(Z700=3, "rd", IF(Z700>3, "th"))))

I have this equation in Col AA. It refers to the previous column. It's working fine except that when Col Z has a blank it still returns th in Col AA. I've tried adding things like IF(Z700 is blank, " ") or IF(Z700= " ", " ") but still the pesky th appears in Col AA. Any help would be apreciated.

 

[Joe4]

Try this:

=IF(LEN(TRIM(Z700))=0,"",IF(Z700=1, "st", IF(Z700=2, "nd", IF(Z700=3, "rd", IF(Z700>3, "th","")))))

 

[richardthelionheart]

Try this:

=IF(LEN(TRIM(Z700))=0,"",IF(Z700=1, "st", IF(Z700=2, "nd", IF(Z700=3, "rd", IF(Z700>3, "th","")))))

Thanks very much. It works perfectly.

 

[Joe4]

You are welcome!

Hope none of your entries goes over 20, or else you have some more work to do with that formula!

 

[FDibbins]

Or this?
=if(z700="","",z700&IF(z700<4,LOOKUP(A11,{1,2,3},{"st","nd","rd"}),"th")