Avoiding Nested 'IF' Statements

M

mlv

Hi,

I have seven cells (A1 - A7) with drop-down lists that allow the user to
select one of a number of text options. The default text option is the word
"None".

I have an eighth cell that needs to display a text message whenever any one
of the seven cells is displaying any text other than "None". That is, if
one of the seven cells is NOT "None", then the message should display.

Is there a more elegant formula for achieving this, other than nesting lots
of 'IF' statements?

Thanks,
 
M

mlv

I don't think I made this quite as clear as I could/should have.

I am currently using:

=IF(OR(NOT(A1="None),NOT(A2="None).... etc.

I'm wondering if there is a function that will look simultaneously at a list
of cells for a specific value.

I'm also looking for some way of solving other formula issues where more
than seven nested 'IF' statements are otherwise needed.

Maybe 'COUNTIF' is the function to use?

Thanks,
 
P

Pete_UK

Another way:

=IF(A1&A2&A3&A4&A5&A6&A7 <>"NoneNoneNoneNoneNoneNoneNone", etc ..

Hope this helps.

Pete
 
R

RagDyeR

I like your first suggestion about Countif:

=If(Countif(A1:A7,"None")<7,"Enter Message Here","")
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


<I'm also looking for some way of solving other formula issues where more
than seven nested 'IF' statements are otherwise needed.>

Look here:

http://spreadsheetpage.com/index.php/tip/alternatives_to_nested_if_functions
 
P

ProfessionalExcel.com

Mike,

If your message doesn't change depending on which cell contains the text
string "None", the following would do the job:

=IF(ISERROR(MATCH("None",A1:A7,0)),"Your Message","")


----------------------------
Please rate this post if it answers your question.

Thanks,

Chris
http://www.ProfessionalExcel.com
 
M

mlv

ProfessionalExcel.com said:
Mike,

If your message doesn't change depending on which cell contains
the text string "None", the following would do the job:

=IF(ISERROR(MATCH("None",A1:A7,0)),"Your Message","")
Click to expand...

Hi Chris,

Thanks, the formula would work, except it needs inverting.

I wanted the message to appear if the value of any one of the cells was not
"None". Your formula requires the value of all of the cells not to be
"None" before the message will display.

Regards,

Mike
 
M

mlv

Hi Niek/Pete/RD,

Thanks for all the input, I have the formula working just fine now.

Regards,
 
Top