• 12 hours
  • Medium

Free online content available in this course.

course.header.alt.is_certifying

Got it!

Last updated on 7/26/23

Tailor Nested Formulas to Your Needs

Tailor Formulas to Your Needs

You now know how to find functions which meet your requirements. Unfortunately, none of them meets your specific needs. 😕

Don’t worry. There is always a way, which most often involves combining different functions. 😉

Download this file which contains sales from your dashboard.

Zarigual Eastern European sales table
Zarigual Eastern European sales table

You need to use this data to give your opinion, based on a number of criteria:

  • If sales from the last four quarters are higher than the four previous quarters, you display “Well done!”

  • If not, you display a hyphen (“-”).

Excel doesn’t seem to have a function which will provide this result, so it’s down to you to build one! 😅 In this case, you’ll need to combine the functions IF() and SUM() by following several steps:

Define the Condition

The condition for the IF() function is shown in the image below:

If blue box > red box, display “Well done!,” otherwise “-”
If blue box > red box, display “Well done!,” otherwise “-”

Select the Main Function

Using the “fx” icon:

  • Click on the “fx” icon to the left of the formula bar, which opens the “Insert Function” tool .

  • Look for the IF() function in the list.

Look for the IF() function in the function list
Look for the IF() function in the function list
  • Click “OK.”

  • Excel displays the arguments selection window.

Arguments window for the IF() function
Arguments window for the IF() function

In the “Logical_test” field, instead of entering a value, you want to enter a calculation for the gap between last year’s sales and the sales of the previous year.

  • Click in the “Logical_test” field.

  • Click on the name box to the left of the formula bar. This box now displays a list of Excel functions.

The name box now displays functions
The name box now displays functions

Select the Secondary Function

This is where you select the nested function, in this case SUM().

  • Click on “SUM.”

  • Excel displays the SUM() arguments window.

In the formula bar, you’ll see that the SUM() function is nested in the IF() function
In the formula bar, you’ll see that the SUM() function is nested in the IF() function
  • Select the cells for the last four quarters, in this case, G6:J6.

  • Click on the “IF” text in the formula bar to go back to the IF() function arguments window.

Go back to the IF() function arguments window, which now contains the SUM() function as the first argument.

The SUM() function is fully nested within the IF() function
The SUM() function is fully nested within the IF() function
  • In the “Logical_test” field, enter “>” to make the comparison between last year and the previous year.

  • Click in the name box again to add the second sum, for the previous year.

  • Now select the C6:F6 cell range.

Select the range for the second SUM() function
Select the range for the second SUM() function
  • Click on the “IF” text on the formula bar to go back to the IF() arguments window.

The logical test is now complete
The logical test is now complete
  • In the “Value_if_true” field, enter “Well done!”

  • In the “Value_if_false” field, enter “-.”

All arguments are now complete
All arguments are now complete
  • Click OK.

“Well done!” is displayed for three clothing categories—what a fantastic result! 😀
“Well done!” is displayed for three clothing categories—what a fantastic result! 😀

Correct Formula Errors

Identify Different Error Types

Excel sometimes displays errors when using functions.

There are a few different errors with strange names, which make them easier to recognize. The main error values include #NAME?, #REF!, #VALUE!, #N/A and #DIV/0!

The VLOOKUP() function can bring up several types of error
The VLOOKUP() function can bring up several types of error

We can see how to correct these error types by displaying the five formulas used:

Examples of erroneous formulas which bring up an error
Examples of erroneous formulas which bring up an error
#NAME?

This error means that you have mistyped the VLOOKUP() function. In this case, there are two Vs.

  • You simply need to correct the formula.

#REF!

This message indicates that the formula refers to a cell which cannot be found. In this example, the third argument of the function refers to column number 8, which is not included in the data range “J:P.”

  • Check the cell references in your formula.

#DIV/0!

The error indicates that Excel is calculating a division by zero, which is impossible.

  • Check the divisions in your formula, and look for zeros.

#N/A

Displays when your formula cannot find what it is looking for. This is a common error when using the VLOOKUP() function. In this case, the garment sought (“Stocks”) contains a syntax error, as there is a ’t’ in what should be “Socks.” Consequently, Excel cannot find this value.

  • Check the data that your formula is looking for.

#VALUE!

This error can be more complicated, as it is a general error which is not linked to a specific problem. In this case, it is the third argument of the VLOOKUP() function, “-7,” which is causing the error. Excel is seeking a positive value rather than a negative value.

  • Check each argument in your formula.

Find Error Cells

The first step is to find error cells and understand what has gone wrong.

Excel can help you with this. Simply use the icon in the “Formulas” tab.

  • Select a worksheet which contains errors.

  • Click on this button.

  • Excel will help you to analyze the errors by finding them and providing an explanation.

Excel helps you to analyze erroneous formulas
Excel helps you to analyze erroneous formulas
  • Click Next to go to the next error.

Anticipate Errors

Sometimes, a formula might throw up an error which is entirely expected. For example:

  • Potential zero value (sales, changes etc.)

  • If the VLOOKUP() function cannot find a match

In this example, you want to calculate the change in sales between the first quarter of the current year and the first quarter of the previous year.

You need to allow for a quarter with no sales, which will bring up the error “#DIV/0!”

Change formula without error management
Change formula without error management

You’ll need to use the IFERROR() function to manage potential formula errors:

  • The formula is: “If the change is an error, display a hyphen, otherwise display the change.”

  • The basic syntax for the change formula is “J7/F7-1.”

  • Display a hyphen in the case of an erroneous result: =IFERROR(J7/F7-1,“-”).

The result becomes more relevant if the errors are not displayed.
The result becomes more relevant if the errors are not displayed.

Over to You!

Download this file and do the following:

  • Populate the “Opinion” column with the text “Great!” when the maximum sales for the last six quarters exceeds 5.2. Otherwise, display “-.” To do so, use the IF() formula with a nested MAX() formula, in the yellow cells.

  • Collect the two error cells (cells with an orange background). The first displays 119%, and the second 139%.

  • Modify the error cell (cell with a blue background) so that it displays a “-” when there is an error. Tip: use the IFERROR() function.

Answer Key

Look at the answer key and watch the video below to check your work.

Let’s Recap!

  • You can nest a function inside another, if you replace an argument with a function. This allows you to tailor functions to your own requirements.

  • Don’t forget to check the formulas in your file, and to correct any errors.

Your tables are now full of error-free formulas, and the cell formats adjust depending on the value. All you need to do now is work on the graphics! 😊

Example of certificate of achievement
Example of certificate of achievement