More than 7 IF statements in one arguement

[smithers2002]

Excel 2003: Should be able to use more than 7 IF statements in one arguement-
I constantly need to use these, but can only enter 7 arguements. Is there a
way of entering more arguements as part of the same formula?

 

[Don Guillett]

if(your7) + if
but it would be better to use a lookup table or choose or something else.
What is your formula?

 

[Arvi Laanemets]

Hi

CHOOSE can have up to 29 responses (values, ranges or expressions) to choose
between.
VLOOKUP can return any number of responses from cell range.
No way to nest more than 7 IF's!


Arvi Laanemets

 

[Ron Rosenfeld]

Excel 2003: Should be able to use more than 7 IF statements in one arguement-

You can. However, you may not NEST more than seven functions of any type.

As Harlan Grove has pointed out in the past, this is a limitation of Excel's
formula parser, as the program itself will evaluate to greater limits, but the
formula has to be created in another program (e.g. Open Office) and cannot be
edited in Excel.

However, since most instances (actually all that I've seen myself) of people
wanting to nest more than seven IF's can be handled more clearly, and edited
much more easily, by using a lookup table (see HELP for VLOOKUP), it is
unlikely that Microsoft will ever change this behavior.


--ron

 

[smithers2002]

Hi all, thanks for responding- What i am trying to do is: I have several
numerical columns of data which need replacing with corresponding text. Eg
Country code 1= India. The problem is, some of the columns have over 200
different codes, so an if statement doesnt work in this instance. Is there
anything you can suggest?

Thanks for your help

 

[Ron Rosenfeld]

Is there anything you can suggest?

A lookup table was already suggested by several posters, along with a
recommendation to check out VLOOKUP in Help.

Did you try this?

Was there a problem using it?



--ron

 

[smithers2002]

Hi Ron,

Have got this sussed now using Vlookup, thanks. You might be able to answer
me another question though- is there anyway of inserting a 'blank' instead of
'False' at the end of the statement. Ideally I would like the spreadsheet to
return blank cells instead of n/a. Would ISERROR work?

Thanks, Lynsey

 

[Niek Otten]

=IF(ISNA(YourFormula),0,YourFormula)

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Debra Dalgleish]

Or, to return an empty string, which would make the cell look blank:

=IF(ISNA(YourFormula),"",YourFormula)

 

[smithers2002]

Thanks all, have got this sorted now- works a treat x