Advanced functions made easy: Nesting functions

Alex Mozes
A free video tutorial from Alex Mozes
Instructor Liaison with Udemy
4.6 instructor rating • 4 courses • 33,011 students

Lecture description

Add capabilities to functions by nesting one function inside another.

Learn more from the full course

Smart Tips: Excel

Quick tips to up your productivity in Excel: Master functions, format your spreadsheets, & analyze data with ease

04:58:19 of on-demand video • Updated November 2019

  • Become more productive with Microsoft Excel
  • Master calculations in excel with a master rule for all functions
  • Simplify formatting to quickly create professionally formatted spreadsheets
  • Discover useful tools like autofill, sparklines, and goal seek
  • Use charts, pivot tables, and other tools to manage lists and analyze data
English [Auto] In the master rule of all functions lecture be introduced the concept of a universal syntax for functions where every one of the 480 plus functions in excel are written the same way with the name of the function. Then open and close parentheses within which code the variables divided by commas. And if these variables that make the function go every function has unique ability whether that is to some arrange find unique values in a list. Or maybe give different results if a value in a cell does or doesn't meet a logical condition. But to truly unlock the potential of Excel it's not enough to just know how to use each of these functions individually. You need to learn how to combine functions together using a concept called nesting. This technique is incredibly powerful as it lets you take advantage of the unique properties of different functions and compound them together to create custom calculations that meet your specific needs. The good news is as long as you remember that universal syntax that we've talked about again name of the function open and closed parentheses within which go the variables divided by commas nesting functions is relatively easy. That's because like a series of Russian dolls every variable inside of every function can in essence be another function. Let's see how this works with a few simple examples which will equip you to experiment with this concept and all sorts of new and complex ways let's start with a simple example in a random cell on a blank sheet there's a function called the today function we saw in the dates lecture that gives you today's date. As an always updating date it has no variables so the syntax is just the word today and open and close parentheses. Now there are also some other great date functions things like the year month day or even week day functions. Each of these only have one variable a date although that weekday function which returns a number corresponding to the day of the week does have an optional return variable that changes the default of starting with Sunday as one to Monday as one. Now let's say that you wanted a cell that always showed the current month a general rule of thumb when nesting cells is to think about the sequence. You need Excel to follow like what is the inside verse outside Russian doll and start with the outside one and work your way in. So if we want the month to be the output we'll start by writing the month formula with our universal syntax equals then the word month then the Open parentheses. Now here's where nesting comes in. You can literally write the today function inside the month function just put today with those open and close parentheses as if it were its own function and then we gotta remember to put that other close parentheses at the end basically instead of a value or a cell reference. We've used a function as the variable Excel will always calculate these functions from the inside out. Meaning first it's going to solve that today function. Then put that result right into the month function and then solve the lung function. So voila we have a single cell to house the month. I can do the same thing with any of those dates functions. We simply nested today function inside of the date function and you could get the current month like we have here or we could do equals day and put the today function in there. And now we see the current day of the month let me demonstrate another usage with a slightly more complex function. Here I have an imaginary list of employee salaries and I want to know how many of my employees make more than the average for all employees. There is a function called the count if function that has two variables in it the range you want to count and then a criteria that you might want to use to determine what to count. I want to count all the salaries that are higher than average of the rest of my salaries. So because my output is a count I start with the count. It function so equals count if and then also the open periods. Now the first variable that this function wants is the range to count. So I'll select all the salaries and then a comma and then the criteria which is basically just a logic question will use a logic symbol of greater then which is above your period and then just like before we're going to nest the average right into this function. So we'll write average and then select the salary range and put that in its own parentheses. Notice now how both functions are still following their universal syntax for their respective functions. Even Notice how their respective parentheses get color coded. We've simply nested one function as a variable in the count function. Now conceptually this would work but one little quirk of some of the logic functions is when you are using logic statements instead of a static value you actually have to do a little bit of a concatenate something we covered in the CONCATENATE lecture. So instead of just the greater than symbol and then the average function we need to put the greater than symbol in quotes and then use the ampersand or n symbol to combine that with the average function so we'll do that here now and now as long as I've got all those parentheses in place hit enter and TR we've combined the power of the average function with the power of the count. If function to count the number of salaries higher than average keep practicing this nested function with thousands of applications you'll suddenly see in your spreadsheets a good sign that you could take advantage of this nesting concept is when you find yourself making a cell or even a whole column to hold one step of a multi-step calculation where you later refer to the answer in that first cell as a variable in another function. Now with nesting functions you can save that effort simplify your sheets and do it all in one nesting function. Enjoy.