how many if statements can i use in a single cell ?

[Guest]

i have the folling formula, but excel doesn't seem to accept it

=IF(Leave!E5="A","A",IF(Leave!E5="I","I",IF(Leave!E5="S","S",IF(Leave!E5="L","L",IF(Leave!E5="T","T",IF(Leave!E5="R","R",IF(Leave!E6="A","A",IF(Leave!E6="I","I",IF(Leave!E6="S","S",IF(Leave!E6="L","L",IF(Leave!E6="T","T",IF(Leave!E6="R","R",IF(Leave!E7="A","x")))))))))))))

i need to have all those letters for each of the 5 cells being referenced...
currently using Excel 2000 9.0.6926 SP-3.

if there is a limitation.. how do i get around it ?

 

[Biff]

Hi!

This is my best guess at what you want:

If any cell in the range Leave!E5:E9 contains any one of the letter
variables, return that letter, otherwise, return "X". Only one cell may
contain a letter.

List the letter variables in a range somewhere, say, A1:A6.

=IF(SUMPRODUCT(COUNTIF(A1:A6,Leave!E5:E9)),INDEX(Leave!E5:E9,MATCH("*",Leave!E5:E9,0)),"X")

There is a limit of 7 nested levels of functions. you had 12.

Biff

 

[Roger Govier]

Hi

One way would be the array formula
{=CHAR(MIN(IF(ISTEXT(Leave!E5:E9&"X"),CODE(Leave!E5:E9&"X"))))}

Commit with Ctrl+Shift+Enter not just Enter when entering or modifying the
formula. Excel will include the curly braces { } for you, do not type them
yourself.

Regards

Roger Govier

 

[Biff]

One way would be the array formula

{=CHAR(MIN(IF(ISTEXT(Leave!E5:E9&"X"),CODE(Leave!E5:E9&"X"))))}

That's case sensitive and also allows for entries other than the 6 specific
variables (including numbers). I guess data validation could take care of
that.

a returns X
B returns B
3 returns 3

Biff

 

[Roger Govier]

Hi Biff

If the data isn't already being validated then
{=CHAR(MIN(IF(CODE(Leave!E5:E9&"X")>64,CODE(UPPER(Leave!E5:E9&"X"))))))}
should do the trick.

Regards

Roger Govier

 

[Roger Govier]

Hi

Meant to say, will take out numerics and lower case, will still not trap for
upper alpha's not in required range of values, but they could be accounted
for by Data Validation.

Regards

Roger Govier