Using Functions in Excel

Working with Nested Functions

Using a function as one of the arguments in a formula that uses a function is called nesting, and we’ll refer to that function as a nested function.

For example, by nesting the AVERAGE and SUM function in the arguments of the IF function, the following formula sums a set of numbers (G2: G5) only if the average of another set of numbers (F2: F5) is greater than 50. Otherwise, it returns 0.

The AVERAGE and SUM functions are nested within the IF function.
You can nest up to 64 levels of functions in a formula.

Consolidating Data Using a 3-D Reference Sum Function

A 3-D reference includes the cell or range reference, preceded by a range of worksheet names. Excel uses any worksheets stored between the starting and ending names of the reference.

Example: =SUM(Sheet2:Sheet13!B5)

Adds all the values contained in cell B5 on all the worksheets between and including Sheet 2 and Sheet 13.

Using the IF Functions

The IF function is one of the most popular functions in Excel, and it allows you to make logical comparisons between a value and what you expect. In its simplest form, the IF function says: 

• IF(Something is True, then do something, otherwise do something else)

So an IF statement can have two results. The first result is if your comparison is True, the second if your comparison is False.

Example:

• =IF(C2=”Yes”,1,2)

In the example, cell D2 says: IF(C2 = Yes, then return a 1, otherwise return a 2)

 

• =IF(C2=1, ”Yes”, ”No”)

In this example, the formula in cell D2 says: IF(C2 = 1, then return Yes, otherwise return No)

As you can see, the IF function can be used to evaluate both text and values. It can also be used to evaluate errors.

You are not limited to only checking if one thing is equal to another and returning a single result, you can also use mathematical operators and perform additional calculations depending on your criteria. You can also nest multiple IF functions together in order to perform multiple comparisons.

Note: If you are going to use text in formulas, you need to wrap the text in quotes (e.g. “Text”). The only exception to that is using TRUE or FALSE, which Excel automatically understands.

Nested IF Functions

IF statements are incredibly robust and form the basis of many spreadsheet models, but they are also the root cause of many spreadsheet issues. Ideally, an IF statement should apply to minimal conditions, such as Male/Female, Yes/No/Maybe, to name a few, but sometimes you might need to evaluate more complex scenarios that require nesting* more than 3 IF functions together.

For your information, “Nesting” refers to the practice of joining multiple functions together in one formula.

While Excel will allow you to nest up to 64 different IF functions, it’s not at all advisable to do so. Why?

• Multiple IF statements require a great deal of thought to build correctly and make sure that their logic can calculate correctly through each condition all the way to the end. If you don’t nest your formula 100% accurately, then it might work 75% of the time, but return unexpected results 25% of the time. Unfortunately, the odds of you catching the 25% are slim. 
• Multiple IF statements can become incredibly difficult to maintain, especially when you come back sometime later and try to figure out what you, or worse someone else, was trying to do.

Below is an example of a relatively standard nested IF statement to convert student test scores to their letter grade equivalent.

• =IF(D2>89,"A",IF(D2>79,"B",IF(D2>69,"C",IF(D2>59,"D","F"))))

This complex nested IF statement follows a straightforward logic:

1. If the Test Score (in cell D2) is greater than 89, then the student gets an A. 
2. If the Test Score is greater than 79, then the student gets a B. 
3. If the Test Score is greater than 69, then the student gets a C. 
4. If the Test Score is greater than 59, then the student gets a D. 
5. Otherwise, the student gets an F.