Avoiding Nested 'IF' Statements

  • Thread starter Thread starter mlv
  • Start date Start date
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,
 
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,
 
Another way:

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

Hope this helps.

Pete
 
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
 
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
 
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
 
Hi Niek/Pete/RD,

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

Regards,
 
Back
Top