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.
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:
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.
Click âOK.â
Excel displays the arguments selection window.
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.
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.
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.
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.
Click on the âIFâ text on the formula bar to go back to the IF() arguments window.
In the âValue_if_trueâ field, enter âWell done!â
In the âValue_if_falseâ field, enter â-.â
Click OK.
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!
We can see how to correct these error types by displaying the five formulas used:
#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.
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!â
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,â-â).
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! đ