Avoiding Nested 'IF' Statements

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,
-- 
Mike
-Please remove 'safetycatch' from email address before firing off your 
reply- 


0
8/13/2009 9:51:50 AM
excel 39879 articles. 2 followers. Follow

9 Replies
553 Views

Similar Articles

[PageSpeed] 31

Use the COUNTIF() function

-- 
Kind regards,

Niek Otten
Microsoft MVP - Excel

"mlv" <mike.safetycatchvincent@jet.co.uk> wrote in message 
news:h60nna$a1v$1@north.jnrs.ja.net...
> 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,
> -- 
> Mike
> -Please remove 'safetycatch' from email address before firing off your 
> reply-
> 

0
nicolaus (2022)
8/13/2009 10:04:45 AM
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,
-- 
Mike
-Please remove 'safetycatch' from email address before firing off your 
reply-

I wrote:
>
> 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,
> -- 
> Mike
> -Please remove 'safetycatch' from email address before firing off your 
> reply-
> 


0
8/13/2009 10:06:03 AM
<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

-- 
Kind regards,

Niek Otten
Microsoft MVP - Excel

"mlv" <mike.safetycatchvincent@jet.co.uk> wrote in message 
news:h60ohv$abi$1@north.jnrs.ja.net...
>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,
> -- 
> Mike
> -Please remove 'safetycatch' from email address before firing off your 
> reply-
>
> I wrote:
>>
>> 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,
>> -- 
>> Mike
>> -Please remove 'safetycatch' from email address before firing off your 
>> reply-
>>
>
> 

0
nicolaus (2022)
8/13/2009 10:13:58 AM
Another way:

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

Hope this helps.

Pete

On Aug 13, 11:13=A0am, "Niek Otten" <nicol...@xs4all.nl> wrote:
> <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_fu...
>
> --
> Kind regards,
>
> Niek Otten
> Microsoft MVP - Excel
>
> "mlv" <mike.safetycatchvinc...@jet.co.uk> wrote in message
>
> news:h60ohv$abi$1@north.jnrs.ja.net...
>
>
>
> >I don't think I made this quite as clear as I could/should have.
>
> > I am currently using:
>
> > =3DIF(OR(NOT(A1=3D"None),NOT(A2=3D"None).... =A0etc.
>
> > 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 mor=
e
> > than seven nested 'IF' statements are otherwise needed.
>
> > Maybe 'COUNTIF' is the function to use?
>
> > Thanks,
> > --
> > Mike
> > -Please remove 'safetycatch' from email address before firing off your
> > reply-
>
> > I wrote:
>
> >> I have seven cells (A1 - A7) with drop-down lists that allow the user =
to
> >> select one of a number of text options. =A0The default text option is =
the
> >> word "None".
>
> >> I have an eighth cell that needs to display a text message whenever an=
y
> >> one of the seven cells is displaying any text other than "None". =A0Th=
at
> >> 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,
> >> --
> >> Mike
> >> -Please remove 'safetycatch' from email address before firing off your
> >> reply-- Hide quoted text -
>
> - Show quoted text -

0
pashurst (2576)
8/13/2009 10:39:27 AM
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!
=====================================================


"Niek Otten" <nicolaus@xs4all.nl> wrote in message 
news:DC75F35A-A16E-4A1D-948A-4EF134254F7A@microsoft.com...
<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

-- 
Kind regards,

Niek Otten
Microsoft MVP - Excel

"mlv" <mike.safetycatchvincent@jet.co.uk> wrote in message
news:h60ohv$abi$1@north.jnrs.ja.net...
>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,
> -- 
> Mike
> -Please remove 'safetycatch' from email address before firing off your
> reply-
>
> I wrote:
>>
>> 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,
>> -- 
>> Mike
>> -Please remove 'safetycatch' from email address before firing off your
>> reply-
>>
>
>


0
ragdyer1 (4060)
8/13/2009 2:14:40 PM
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


"mlv" wrote:

> 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,
> -- 
> Mike
> -Please remove 'safetycatch' from email address before firing off your 
> reply- 
> 
> 
> 
0
8/13/2009 3:53:02 PM
ProfessionalExcel.com wrote:
>
> 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","")

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 


0
8/13/2009 6:12:16 PM
Hi Niek/Pete/RD,

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

Regards,
-- 
Mike
-Please remove 'safetycatch' from email address before firing off your 
reply- 


0
8/13/2009 6:14:20 PM
And we appreciate your feed-back.
-- 
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"mlv" <mike.safetycatchvincent@jet.co.uk> wrote in message 
news:h61l5g$ijq$1@north.jnrs.ja.net...
> Hi Niek/Pete/RD,
>
> Thanks for all the input, I have the formula working just fine now.
>
> Regards,
> -- 
> Mike
> -Please remove 'safetycatch' from email address before firing off your 
> reply-
> 


0
ragdyer1 (4060)
8/13/2009 6:49:48 PM
Reply:

Similar Artilces:

Data Validation and IF Statement
Hi, I have an Excel spreadsheet that handles an expense report. I set up Data Validation in Column A to pull entries from a list: AutoFill Self Full Service. This works fine. I would like to use data validation or an IF statement in Column B s that when "AutoFill" is selected in Column A, another list appears i Column B for the user to select entries from say: BP Speedway Shell I only want the list in Column B to come up if "AutoFill" is selecte in Column A. Is this possible? Thanks, Debbi Attachment filename: expense.xls Download at...

XL03: Avoid "workbook contains links" msg when opening
'Open workbook Application.DisplayAlerts = False Set twb = Workbooks.Open(FileName:=MyFullFilePath, ReadOnly:=True) Application.DisplayAlerts = True When my target workbook contains links, I still get a warning pop-up asking if links should be updated, even with displayalerts = False. So I changed the open statement to: Application.DisplayAlerts = False Set twb = Workbooks.Open(FileName:=MyFullFilePath, UpdateLinks:=False, ReadOnly:=True) Application.DisplayAlerts = True It bypasses that initial warning, but then pops up the filesearch wizard/userform with the title ba...

Excel
I want to record new data by date so that the progress of my spreadsheet can be easily seen. I do not want to save hundreds of copies of my spreadsheet - e.g. Save As. Can anybody help ? ...

Not good with IIF statements
I am in the need of an IIF statement. I am looking to do the following: If the field [Current CommTech Level] equals CommTech 1 then add 90 days to the field [Date of Last Promotion]. If not then the field is blank. Any help would be great. Thank you all. -- Scot Rawlings Technical Trainer Comcast Auburn, WA Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200803/1 IIF([Current CommTech Level] = [CommTech 1], [Date of Last Promotion] = DateAdd("d", 90, [Date of Last Promotion]), Null) BTW, spaces in names are not a good idea....

For statement behaving very strangely
Hi All, The following For...Next statement appears to be giving the incorrect results. My original code was to loop through rows 6 to 1200 in column AB and hide each row whre the value was 0. I found that the 1st 900 or so rows did not appear to be evaluated but the remaining 300 did and the rows were hidden. Further testing and use of the immediate window showed that indeed this was true. If l change the number of rows as being 6 to 100 and/or 6 to 250 all rows are evaluated as expected. As soon as l increase the number of rows above 250 l get unexpected results. Does anybody know ...

Put format condition in IF statement?
Hi All With some help from people in this NG I have managed (using data validation) to trap an error where the user does not use the correct time format (hh:mm). Basic setup: time values (format hh:mm) in A1 and A2. A3 (formatted as number) contains formula: =A1-A2. OK, but if user types too quickly and enters 1400 in A1 instead of 14:00, A3 displays "rubbish". The program (graph based in part on value in A3) doesn't fail because the user is forced to retry, but the rubbish in A3 is still visible alongside the data validation dialogue box, so looks untidy. Is there a way to pu...

Average IFS (Multiple Criteria)
I have used the following formula and it works prefectly, however, one of my columns contains % and I want an average not a sum. Is there a way to use AVERAGEIFS to get the average only if other cells contain certain criteria? =SUMIFS(AF$4:AF$169,$G$4:$G$169,$G174,$F$4:$F$169,$F174,$J$4:$J$169,"<=12/31/10",$J$4:$J$169,">=01/01/10") ...

Hyperlink
I have lots of hyperlinks in excel worksheet. Each time I click on them I get an alert looking question: "Hyperlinks can be harmfull to your computer and data, (...) do you wish to continue?" User must choose Yes or No. I tried Application.DisplayAlerts = False but of course it does not work - this is a question, not an alert. Does any one have an idea of how to couple with this problem? Your suggestions are welcomed. Topola Hi Topola, Check if the following link helps you solve your problem. http://support.microsoft.com/?kbid=829072 Thanks -Rama topola wrote: > I have l...

Money 2004 does not see downloaded bank statement...
I just upgraded (a couple of weeks ago) from 2003 to 2004. I am running Windows XP Pro. In 2003, I used to use Internet Explorer, go to my bank, select my date range, and download my transactions for "Money 99 or higher". If I had Money 2003 open, it would automatically see the downloaded file and import the transactions. I just did the EXACT same thing - about 10 times - in Money 2004 and it does NOT see the downloaded file or import the transactions. The download happens so fast that I don't know where the browser saved off the file. A quick search of the hard drive d...

IF Statement, Ranges and SUM
Hi, I have a spreadsheet where in I want to check the value in column A and then find the associated value in column B and SUM up. For example: Col A Col B Soup 10 Cake 10 Soup 10 Cake 10 So I want to check the range of col A for Soup and return the value of 20. Currently my if statement is returning 40. "paddy_nyr" <philpott.patrick@gmail.com> wrote: > I have a spreadsheet where in I want to check the value > in column A and then find the associated value in column B > and SUM up. [....] > So I want to check ...

Avoid weekend formula results
Greetings to all, Does anyone know of a formula to that will calculate a date that when the result falls on a weekend (Saturday or Sunday)it will give the Friday date before. For example, if I have January 19, 2004 entered in cell A1 and I have a formula in cell A2 that reads =A1+5, I would like the result to be January 23, 2004 because it is the Friday before instead of the natural result of January 24, 2004. Anyone know how to modify the formula for this. Thanks, SAL Hi Sal assuming your date is in A1 you can use the following =A1-(IF(WEEKDAY(A1,2)>5,WEEKDAY(A1,2)-5)) HTH Fr...

SHAME ON MICROSOFT for discontinuing downloadable statements!
Is anyone else as ticked off with Microsoft for discontinuing downloadable statements MID-YEAR ? I totally understand if the business model does not allow a company the ability to contine a product, but KEEP THE SYSTEM GOING AT LEAST LONG ENOUGHT TO COMPLETE THE YEAR! BAD, bad business practices! Shame on you Microsfoft. ...

#Value! Error in Nested IF Function
I have to IF Functions that work independently and I am trying to combine them into one function. My desired end result is either the text "BUY" or "SELL". 1st Function - IF(AND($B$13="High",$I$13="S"),"SELL","") If cell B13 has the text "High" and I13 has the text "S", then generate the word "SELL". If this is false, then leave the cell blank. 2nd Function - IF(AND($C$9="Low",$I$9="L"),"BUY","") If cell C13 has the text "low" and I13 has the text &q...

How do I avoid multiple icons of Excel when using short command Al
Hi all, I have used Office 2000 several years but now I have upgraded to office 2003. Everything works great but when I use the short command Alt+Tab I get multiples of Excel. I'm used to get only one icon if I have opened only one program and not one iceon per spreadsheet when I use Alt+Tab. Is there a way to disable this annoying feature in Office 2003? Thanks in advance You might have "Windows in Taskbar" selected under Tools, Options, View. -- Jim "DVA" <DVA@discussions.microsoft.com> wrote in message news:E21F2F09-0EA0-4C99-8676-CA12F8264355@micro...

Manual statement download after banking update
After July 19th update bank account institution no longer listed. Followed instuctions for update to work for Money 2007 with this and other accounts other institutions that did not show password text box. Since the one bank was no longer listed tried to manually download statement from using OFX file. File downloaded and text box appeared as usual stating file import complete, but file did not update in account register and no register review shown on money home page. Repeated several times with no success. Other accounts with institution listed by Money work properly and have correct...

Printing statement addresses
Our Client does not want to use specific address fields or use the address lines in report writer, they are currently using the rw_SelectAddrLine function as directed by the Microsoft website but this does not seem to produce consistent results with all addresses. They want to print Statement Addresses in a consistently tidy format and this seems to be a problem. Our client has asked that we inform Microsoft of this problem and ask that it is looked at. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for th...

how do i copy formula down columns avoiding hidden cells
hello, i need to copy a formula down a column in a spread sheet, but i need to avoid putting it in to cells that i have filtered out. Is this possible to do? any help would be great. Just fill it down through unfiltered rows and you will see that hidden rows remain unfilled! Regards, Stefi „PETE” ezt írta: > hello, i need to copy a formula down a column in a spread sheet, but i need > to avoid putting it in to cells that i have filtered out. > Is this possible to do? > any help would be great. ...

Find DL's with nested groups
We will be moving to a new org and the admt tool does not do nested groups very well. I have a file containing all my mail enabled groups. Are there any scripts/tools that will give only the groups that have nested groups and the nested group names. ...

Crash when printing one specific client statement
Has anyone had a problem with one specific client becoming corrupted so you cannot print their statement? -- Christopher Fazio VMD Chris Fazio | 2009-03-01 | 5:29:32 PM wrote: >Has anyone had a problem with one specific client becoming corrupted >so you cannot print their statement? I have not seen that problem. I assume you're using one of the Business versions of Money. If you give us specifics of your issue, maybe we can help figure it out. Tell us things like: * Type of hardware * Operating system version * Version of Money (Help > About) * Exactly what happens ...

can i setup if, then statements to change the color of a row
I would like to use if and then statements to recognize P, O, or C if the cell has P, O, or C I want the row to change colors Blue, Green, Red Is this possible Use Conditional Formatting with the three conditions. Regards Trevor "Ed" <Ed@discussions.microsoft.com> wrote in message news:97B02B64-74A1-4C2D-9E4D-BB141F26994D@microsoft.com... >I would like to use if and then statements to recognize P, O, or C > if the cell has P, O, or C I want the row to change colors Blue, Green, > Red > Is this possible Select a row or range in a row. Then assuming A1 i...

Select Inner.* from nested(outer)-nested(inner) query
Hi, Is it possible to "SELECT *" just the fields of a sub-nested query (i.e., I don't want * of a nested query, but of a nested-nested query)? For example (SELECT Inner.*): ---------------------------- SELECT Inner.* FROM ( SELECT Inner.* , ROW_NUMBER() OVER ( ORDER BY x ) AS ROW FROM ( SELECT x FROM y ) AS Inner WHERE ROW BETWEEN @Start AND @End ) AS Outer ---------------------------- So I want the result to be everything from the sub-nested query (and not the extra fields added in the nes...

print statements in originating currency
We currently have 6 currencies within one company and need to print statement for so they are in the customers currency that they were billed in. I thought for sure there would be an Originating Document Amount (ORDOCAMT) field in the ASI12305 table. I'm sure others have this same issue and if this is not going to be cahnged in the near future can you direct me to a third party solution that can help me in the mean time. Thanks for your help. -Danny Clemenson ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes....

If..Then Statement Problem
I have figured out the first part of this problem, but can't seem to ge any farther. Here's what I need Basic synopsis: each client has a different percentage to which thei invoices are multiplied, i.e., Column A is the client name, Column B i the total invoice amount, Column C is the percentage of the invoice. If Column A = "AAA" then the percentage is 50%, so whatever Column is, Column C will calculate at 50%. If Column A = "BBB" then th percentage is 75%, and whatever is inColumn B will calculate int Column C at 75% I have gotten as far as if Column A = &q...

what is 'tns' in this statement in this wsdl doc?
if I can ask that in this newsgroup? <input message="tns:addRestaurantIn"/> what is the tns used for here in this wsdl? Thank you thank you.. -hazz hazz wrote: > if I can ask that in this newsgroup? > > <input message="tns:addRestaurantIn"/> > > what is the tns used for here in this wsdl? Thank you tns here is a namespace prefix which binds addRestaurantIn name to a particular namespace, declared using xmlns:tns="..." declaration. "tns" prefix usually used by XSD generating tools to denote target namespace. -- ...

If-statement (deal with date and time)
Hi all, What formula should I input into the cell if I want to check th current date and time against a given date and time, i.e. If current date and time equals a month (say, June) then display a formula (say, = A1 + B2) else 0 I have wrote such code: = IF(MONTH(NOW()) = -a month- , -formula- , 0) but it does not work. I am using xl2000. Thanks -- Message posted from http://www.ExcelForum.com The 'month()' function returns a number from 1-12, so make sure you if() function checks for a number, not the name of the month. Alternatively, you can define all your months as number...