IF clauses

Version 1.9

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Support promotion: Post a question within 7 days of attending your course and get an upgrade to 12 months support! (Standard period 3 months)

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.

IF clauses

Erik has attended:
Excel Introduction course
Excel Intermediate course
Excel Advanced course
Excel VBA course

by - delegate Erik [2 posts] (2008 Jun 18 Wed, 16:40) Reply

Is it possible to use cascading IF statements?

Version	Date	Location	Places available	Book	Next place rate:
Excel VBA 2 day course
Version	Date	Location	Places available	Book	Card	Invoice
2003	2009 Jan 22 Thu + 23 Fri	Southwark	0	FULL
2003	2009 Jan 29 Thu + 30 Fri	Bayswater	4	book now	£475	£485
2007	2009 Feb 5 Thu + 6 Fri	Bloomsbury	5	book now	£485	£490
2003	2009 Feb 19 Thu + 20 Fri	Southwark	4	book now	£475	£485
2003	2009 Feb 26 Thu + 27 Fri	Bayswater	8	book now	£335	£365
2003	2009 Mar 12 Thu + 13 Fri	Bloomsbury	7	book now	£345	£399
Full Schedule: See all 43 Excel VBA course dates. Bookings currently available until 18th March 2010.

RE: IF clauses

by - trainer Pete platinum contributer [868 posts] (2008 Jul 7 Mon, 08:42) Edited on 2008 Jul 7 Mon, 08:43 Reply

Hi Erik, Thank you for your post,

Wow you took the lot in a short period of time, I take my hat off to you, I hope it didnt cause too much brain damage. :)

I have not come across Cascading IF statements, I have seen Cascading data: to do this you would use the Filter option which will do what you want; Available from the Data menu.

Another option would be to use a pivot table although that depends on the layout of your table. I would suggest the filter option using the autofilter to see if that is exactly what you need, there are other options available if you want to automate the process but this is the simplest.

In answer to your question, if however you mean "Nested IF" you should have covered this subject in your Advanced course, but I will go over it again just for others who may be interested.

Excel's IF function provides some simple decision-making capability to a worksheet. The IF function accepts three arguments:

The condition being evaluated (should result in either TRUE or FALSE)
The value to display if the condition is TRUE
The value to display if the condition is FALSE
The formula below, for example, returns 1 if cell A1 contains "A". If cell A1 does not contain "A", the formula returns an empty string.

=IF(A1="A",1,"")

For more decision-making power, you can "nest" IF functions within a formula. In other words, you can use an IF function as the second argument for an IF function. Here's an example:

=IF(A1="A",1,IF(A1="B",2,IF(A1="C",3,"")))

This formula checks cell A1. If it contains "A", the formula returns 1. If it doesn't contain "A", then the second argument is evaluated. The second argument contains another IF function that determines if A1 contains a "B". If so, the formula returns 2; if not, the formula evaluates the IF function contained in the second argument and checks to see if A1 contains "C". If so, it returns 3; otherwise, it returns an empty string.

Excel allows up to seven levels of nested IF functions. The formula below works correctly, but Excel will not allow you to nest the IF functions any deeper than this.

=IF(A1="A",1,IF(A1="B",2,IF(A1="C",3,IF(A1="D",4,
IF(A1="E",5,IF(A1="F",6,IF(A1="G",7,IF(A1="H",8,""))))))))

I hope that answers your question, if so please click the resolved link, regards Pete

IF clauses

Related articles

Excel tip:

Hide data in Excel Worksheets