Leveraging Excel Functions
Functions are essentially canned formulas that Excel provides as a way to accomplish common tasks. Some Excel functions perform simple calculations, saving you from having to enter the formula yourself. Other functions perform complex actions that would be impossible to perform with simple formulas alone.
As you go through the chapters in this book, you explore many of the most useful Excel functions. For now, you start with a basic understanding of the role Excel functions play in your formulas.
Why to use Excel functions
One of the key benefits of using Excel functions is that they help simplify your formulas. For example, if you wanted to get the average of the values in cell A1, A2, and A3, you could enter this formula:
=(A1+A2+A3)/3
This particular formula isn’t too bad, but what if you had to get the average of 100 values? How cumbersome would that formula be to create and manage?
Luckily, Excel has an AVERAGE function. With the AVERAGE function, you can simply enter this formula:
=AVERAGE(A1:A3)
If you had to get the average of 100 values, you could simply expand the range:
=AVERAGE(A1:A100)
Another key benefit of using functions is that they help you accomplish tasks that would be impossible with standard formulas. For instance, imagine that you wanted a couple of formulas that would automatically return the largest and smallest numbers in a range of cells. Sure, if your range was small enough, you could eyeball the largest and smallest numbers. But that’s hardly automated.
There is no nonfunction-driven formula you could possibly enter that would automatically return the largest or smallest number in a range.
Excel’s MAX and MIN functions, however, make short work of this task. The MAX function returns the largest number, whereas the MIN function returns the smallest.
=MAX(A1:A100)
=MIN(A1:A100)
Functions can also help save time by helping you automate tasks that would take you hours to accomplish manually. For example, say that you needed to extract the first 10 characters of a customer number. How long do you think it would take you to go through a table of 1,000 records and get a list of customer numbers that contains only the first 10 characters?
The LEFT function can help here by pulling out the left 10 characters:
=LEFT(A1,10)
You can simply enter this formula for the first row of your table and then copy it down to as many rows you need.
Understanding function arguments
Most of the functions found in Excel require some input or information in order to calculate correctly. For example, to use the AVERAGE function, you need to give it a range of numbers to average.
=AVERAGE(A1:A100)
Any input you give to a function is called an argument. The basic construct of a function is:
Function_Name(argument1, argument2,…)
To use a function, you enter its name, open parenthesis, the needed arguments, and then the close parenthesis. The number of arguments needed varies from function to function.
Using functions with no arguments
Some functions, such as the NOW() function, don’t require any arguments. To get the current date and time, you can simply enter a formula like this:
=NOW()
Note that even though no arguments are required, you still need to include the open and close parentheses.
Using functions with one or more required arguments
Some functions require one or more arguments. The LARGE function, for instance, returns the nth largest number in a range of cells. This function requires two arguments: a cell reference to a range of numeric values and a rank number. To get the third largest value in range A1 through A100, you can enter:
=LARGE(A1:A100,3)
Note that each argument is separated by a comma. This is true regardless of how many arguments you enter. Each argument must be separated by a comma.
Using functions with both required and optional arguments
Many Excel functions, such as the NETWORKDAYS function, allow for optional arguments in addition to the required arguments. The NETWORKDAYS function returns the number of workdays (days excluding wee
Functions are essentially canned formulas that Excel provides as a way to accomplish common tasks. Some Excel functions perform simple calculations, saving you from having to enter the formula yourself. Other functions perform complex actions that would be impossible to perform with simple formulas alone.
As you go through the chapters in this book, you explore many of the most useful Excel functions. For now, you start with a basic understanding of the role Excel functions play in your formulas.
Why to use Excel functions
One of the key benefits of using Excel functions is that they help simplify your formulas. For example, if you wanted to get the average of the values in cell A1, A2, and A3, you could enter this formula:
=(A1+A2+A3)/3
This particular formula isn’t too bad, but what if you had to get the average of 100 values? How cumbersome would that formula be to create and manage?
Luckily, Excel has an AVERAGE function. With the AVERAGE function, you can simply enter this formula:
=AVERAGE(A1:A3)
If you had to get the average of 100 values, you could simply expand the range:
=AVERAGE(A1:A100)
Another key benefit of using functions is that they help you accomplish tasks that would be impossible with standard formulas. For instance, imagine that you wanted a couple of formulas that would automatically return the largest and smallest numbers in a range of cells. Sure, if your range was small enough, you could eyeball the largest and smallest numbers. But that’s hardly automated.
There is no nonfunction-driven formula you could possibly enter that would automatically return the largest or smallest number in a range.
Excel’s MAX and MIN functions, however, make short work of this task. The MAX function returns the largest number, whereas the MIN function returns the smallest.
=MAX(A1:A100)
=MIN(A1:A100)
Functions can also help save time by helping you automate tasks that would take you hours to accomplish manually. For example, say that you needed to extract the first 10 characters of a customer number. How long do you think it would take you to go through a table of 1,000 records and get a list of customer numbers that contains only the first 10 characters?
The LEFT function can help here by pulling out the left 10 characters:
=LEFT(A1,10)
You can simply enter this formula for the first row of your table and then copy it down to as many rows you need.
Understanding function arguments
Most of the functions found in Excel require some input or information in order to calculate correctly. For example, to use the AVERAGE function, you need to give it a range of numbers to average.
=AVERAGE(A1:A100)
Any input you give to a function is called an argument. The basic construct of a function is:
Function_Name(argument1, argument2,…)
To use a function, you enter its name, open parenthesis, the needed arguments, and then the close parenthesis. The number of arguments needed varies from function to function.
Using functions with no arguments
Some functions, such as the NOW() function, don’t require any arguments. To get the current date and time, you can simply enter a formula like this:
=NOW()
Note that even though no arguments are required, you still need to include the open and close parentheses.
Using functions with one or more required arguments
Some functions require one or more arguments. The LARGE function, for instance, returns the nth largest number in a range of cells. This function requires two arguments: a cell reference to a range of numeric values and a rank number. To get the third largest value in range A1 through A100, you can enter:
=LARGE(A1:A100,3)
Note that each argument is separated by a comma. This is true regardless of how many arguments you enter. Each argument must be separated by a comma.
Using functions with both required and optional arguments
Many Excel functions, such as the NETWORKDAYS function, allow for optional arguments in addition to the required arguments. The NETWORKDAYS function returns the number of workdays (days excluding wee
kends) between a given start date and end data.
To use the NETWORKDAYS function, you need to provide, at minimum, the start and end dates. These are the required arguments.
The following formula gives you the answer 260, meaning that there are 260 workdays between January 1, 2014, and December 31, 2014:
=NETWORKDAYS("1/1/2014", "12/31/2014")
The NETWORKDAYS function also allows for an optional argument that lets you pass a range containing a list of holiday dates. The function treats each date in the optional range as a nonworkday, effectively returning a different result (255 workdays between January 1, 2014, and December 31, 2014, taking into account holiday dates).
=NETWORKDAYS("1/1/2014", "12/31/2014", A1:A5)
Don’t be too concerned with completely understanding the NETWORKDAYS function. The take-away here is that when a function has required and optional arguments, you can elect to use the function with just the required arguments, or you can take advantage of the function’s additional utility by providing the optional arguments.
To use the NETWORKDAYS function, you need to provide, at minimum, the start and end dates. These are the required arguments.
The following formula gives you the answer 260, meaning that there are 260 workdays between January 1, 2014, and December 31, 2014:
=NETWORKDAYS("1/1/2014", "12/31/2014")
The NETWORKDAYS function also allows for an optional argument that lets you pass a range containing a list of holiday dates. The function treats each date in the optional range as a nonworkday, effectively returning a different result (255 workdays between January 1, 2014, and December 31, 2014, taking into account holiday dates).
=NETWORKDAYS("1/1/2014", "12/31/2014", A1:A5)
Don’t be too concerned with completely understanding the NETWORKDAYS function. The take-away here is that when a function has required and optional arguments, you can elect to use the function with just the required arguments, or you can take advantage of the function’s additional utility by providing the optional arguments.
Finding out which arguments are needed for a given function
An easy way to discover the arguments needed for a given function is to begin typing that function into a cell. Click a cell, enter the equal sign, enter the function name, and then enter an open parenthesis. Recognizing that you are entering a function, Excel activates a tooltip (see Figure 1-4) that shows you all the arguments for the function. Any argument that is shown in brackets ([ ]) is an optional argument. All others shown without the brackets are required arguments.
Figure 1-4: The function tooltip is a handy way to find out the required and optional arguments for a function.
Getting Help from the Insert Function Wizard
If you find that you’re stuck on which function to use, or aren’t sure of the syntax needed for a particular function, you can use Excel’s Insert Function feature.
Place your cursor in the cell you want to enter a function in and click the Insert Function command, found on the Formulas tab. Alternatively, you can press Shift+F3 to call up the Insert Function dialog box, shown in Figure 1-5. The idea here is to find the function you need and double-click it.
Figure 1-5: The Insert Function dialog box.
If you’re not sure which function you need, you can use the search field at the top of the dialog box to find the most appropriate function based on a keyword. Simply enter one or more keywords and click the Go button. The list of functions will change to display those that best match your search criteria. For example, entering the search term “loan payment” results in functions that perform loan calculations.
If you need to use a function that you’ve recently implemented, you can skip the search feature and simply select “Most Recently Used” from the category drop-down box.
The list of functions changes to display those you’ve recently utilized.
You can also use the category drop-down box to select a category of functions. For instance, selecting “Statistical” from the category drop-down box displays all the statistical functions.
When you find the function that you want to use, double-click it to activate the Function Arguments dialog box (see Figure 1-6). This dialog box serves as a kind of wizard, guiding you through the arguments needed for the selected function.
Figure 1-6: The Function Arguments dialog box guides you through creating your Excel functions.
For each required argument, enter an appropriate value or cell reference in the respective input boxes. Note that the required arguments are always listed first and shown in bold type. Do the same thing for any optional argument that you want to utilize.
Click the OK button to apply your newly configured function to your target cell.
An easy way to discover the arguments needed for a given function is to begin typing that function into a cell. Click a cell, enter the equal sign, enter the function name, and then enter an open parenthesis. Recognizing that you are entering a function, Excel activates a tooltip (see Figure 1-4) that shows you all the arguments for the function. Any argument that is shown in brackets ([ ]) is an optional argument. All others shown without the brackets are required arguments.
Figure 1-4: The function tooltip is a handy way to find out the required and optional arguments for a function.
Getting Help from the Insert Function Wizard
If you find that you’re stuck on which function to use, or aren’t sure of the syntax needed for a particular function, you can use Excel’s Insert Function feature.
Place your cursor in the cell you want to enter a function in and click the Insert Function command, found on the Formulas tab. Alternatively, you can press Shift+F3 to call up the Insert Function dialog box, shown in Figure 1-5. The idea here is to find the function you need and double-click it.
Figure 1-5: The Insert Function dialog box.
If you’re not sure which function you need, you can use the search field at the top of the dialog box to find the most appropriate function based on a keyword. Simply enter one or more keywords and click the Go button. The list of functions will change to display those that best match your search criteria. For example, entering the search term “loan payment” results in functions that perform loan calculations.
If you need to use a function that you’ve recently implemented, you can skip the search feature and simply select “Most Recently Used” from the category drop-down box.
The list of functions changes to display those you’ve recently utilized.
You can also use the category drop-down box to select a category of functions. For instance, selecting “Statistical” from the category drop-down box displays all the statistical functions.
When you find the function that you want to use, double-click it to activate the Function Arguments dialog box (see Figure 1-6). This dialog box serves as a kind of wizard, guiding you through the arguments needed for the selected function.
Figure 1-6: The Function Arguments dialog box guides you through creating your Excel functions.
For each required argument, enter an appropriate value or cell reference in the respective input boxes. Note that the required arguments are always listed first and shown in bold type. Do the same thing for any optional argument that you want to utilize.
Click the OK button to apply your newly configured function to your target cell.
Understanding Formula Errors
It’s not always smooth sailing when you’re working with formulas. Sometimes a formula returns an error value instead of the value you were expecting. Excel helps you identify what the problem may be by returning one of seven error values: #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, and #VALUE!, explained in the following list:
#DIV/0!: This error value means that the formula is attempting to divide a value by zero. There is mathematically no way to divide a number by zero. You will also see this error if the formula is trying to divide a value by an empty cell.
#N/A: This error value means that the formula cannot return a legitimate result. You would typically see this error when you use an inappropriate argument in a function. You will also see this error when a lookup function does not return a match.
#NAME?: This error value means Excel doesn't recognize a name you used in a formula as a valid object. This error could be a result of a misspelled function, a misspelled sheet name, a mistyped cell reference, or some other syntax error.
#NULL!: This error value means the formula uses an intersection of two ranges that don’t intersect.
#NUM!: This error value means there is a problem with a number in your formula; typically an invalid argument in a math or trig function. For example, you entered a negative number where a positive number was expected.
#REF!: This error value means that your formula contains an invalid cell reference. This is typically caused by deleting a row or column to which the formula refers. This could also mean that the formula uses a cell reference that doesn’t exist (A2000000, for instance).
#VALUE: This error value means that your formula uses the wrong data type for the operation it’s trying to do. For example, this formula will return a #VALUE error (=100+”dog”).
Using Named Ranges in Formulas
A named range is nothing more than a cell or range of cells that has been given a friendly, descriptive name. Naming your ranges allows you use easily recognizable names in your formulas instead of cell addresses. For instance, say that you have line- item sales in cells A1:A25 and you have a percent tax in cell B1. You could calculate a total sale amount with tax using this formula:
=SUM(A1:A25)*(1+B1)
Now imagine that you gave your ranges descriptive names, calling cells A1:25 Sale_Items, and calling cell B1 Tax_Percent. You could then calculate the total sale amount with tax by using this formula:
=SUM(Sales_Items)*(1+Tax_Percent)
Immediately, you can see how much easier it is to understand what is going on in the formula. The formula is easier not only to read but also to explain to others who aren’t familiar with the workbook.
Another benefit to naming these ranges is that creating new formulas with these named ranges becomes easier because you can simply use the easily remembered descriptive name instead of trying to remember that line-item sales live in cells A1:A25.
Creating a named range
Follow these steps to create a named range:
Select the cell or range of cells you want to name.
Choose Define Name from the Formulas tab. This activates the New Name dialog box, shown in Figure 1-7.
In the Name input box, enter a friendly, descriptive name for your range.
In the Scope drop-down box, select whether you want your named range to be available for use throughout the workbook or just on a specific sheet.
Press the OK button to create your named range.
It’s not always smooth sailing when you’re working with formulas. Sometimes a formula returns an error value instead of the value you were expecting. Excel helps you identify what the problem may be by returning one of seven error values: #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, and #VALUE!, explained in the following list:
#DIV/0!: This error value means that the formula is attempting to divide a value by zero. There is mathematically no way to divide a number by zero. You will also see this error if the formula is trying to divide a value by an empty cell.
#N/A: This error value means that the formula cannot return a legitimate result. You would typically see this error when you use an inappropriate argument in a function. You will also see this error when a lookup function does not return a match.
#NAME?: This error value means Excel doesn't recognize a name you used in a formula as a valid object. This error could be a result of a misspelled function, a misspelled sheet name, a mistyped cell reference, or some other syntax error.
#NULL!: This error value means the formula uses an intersection of two ranges that don’t intersect.
#NUM!: This error value means there is a problem with a number in your formula; typically an invalid argument in a math or trig function. For example, you entered a negative number where a positive number was expected.
#REF!: This error value means that your formula contains an invalid cell reference. This is typically caused by deleting a row or column to which the formula refers. This could also mean that the formula uses a cell reference that doesn’t exist (A2000000, for instance).
#VALUE: This error value means that your formula uses the wrong data type for the operation it’s trying to do. For example, this formula will return a #VALUE error (=100+”dog”).
Using Named Ranges in Formulas
A named range is nothing more than a cell or range of cells that has been given a friendly, descriptive name. Naming your ranges allows you use easily recognizable names in your formulas instead of cell addresses. For instance, say that you have line- item sales in cells A1:A25 and you have a percent tax in cell B1. You could calculate a total sale amount with tax using this formula:
=SUM(A1:A25)*(1+B1)
Now imagine that you gave your ranges descriptive names, calling cells A1:25 Sale_Items, and calling cell B1 Tax_Percent. You could then calculate the total sale amount with tax by using this formula:
=SUM(Sales_Items)*(1+Tax_Percent)
Immediately, you can see how much easier it is to understand what is going on in the formula. The formula is easier not only to read but also to explain to others who aren’t familiar with the workbook.
Another benefit to naming these ranges is that creating new formulas with these named ranges becomes easier because you can simply use the easily remembered descriptive name instead of trying to remember that line-item sales live in cells A1:A25.
Creating a named range
Follow these steps to create a named range:
Select the cell or range of cells you want to name.
Choose Define Name from the Formulas tab. This activates the New Name dialog box, shown in Figure 1-7.
In the Name input box, enter a friendly, descriptive name for your range.
In the Scope drop-down box, select whether you want your named range to be available for use throughout the workbook or just on a specific sheet.
Press the OK button to create your named range.
Keep these rules and best practices in mind when choosing a name for your range:
You cannot use spaces in range names. Use an underscore to emulate a space instead (for example, Sales_Items).
Range names must begin with a letter or an underscore.
Range names cannot be the same as cell addresses. For instance, you cannot name your range Q1 because Excel already has a cell Q1
You can use any single letter as a range name except for R and C. These are reserved in Excel for the R1C1 reference style.
You cannot use operator symbols (+, –, *, /, <, >, &) in range names. The only symbols valid in range names are the period (.), question mark (?), underscore (_), and backslash (\) symbols, as long as they are not used as the first character of the name.
Avoid using names that Excel uses internally, for example, Print_Area. Although Excel allows this name, using it can cause name conflict errors in the workbook. Other names to avoid are Auto_Activate, Auto_Close, Auto_Deactivate, Auto_Open, Consolidate_Area, Criteria, Data_Form, Database, Extract, FilterDatabase, Print_Titles, Recorder, and Sheet_Title.
The maximum length for a range name is 255 characters. That being said, you should avoid very long range names in general. Remember that the purpose of a range name is to provide a meaningful, easy-to-remember name that you can easily type into a formula.
Working with the Name Box
The Name Box, found to the left of the Formula Bar, offers a couple of handy features for working with named ranges. You can click the drop-down selector in the Name Box to see all the named ranges in your workbook (see Figure 1-8). Clicking any of the named ranges in the list automatically selects that range.
Figure 1-8: Use the Name Box to view and navigate to any named range within the workbook.
The Name Box also serves as a faster way to create a named range. To create a named range with the Name Box, first select the cell or range you want to name. Next, enter a valid name directly into the Name Box. Press the Enter key to create the name.
Tip: The Name Box is resizable. If you have a name that is too long for the Name Box, simply move your mouse cursor over the right edge of the Name Box until it turns into a horizontal arrow. When your cursor becomes a horizontal arrow, click and drag to widen the Name Box.
You cannot use spaces in range names. Use an underscore to emulate a space instead (for example, Sales_Items).
Range names must begin with a letter or an underscore.
Range names cannot be the same as cell addresses. For instance, you cannot name your range Q1 because Excel already has a cell Q1
You can use any single letter as a range name except for R and C. These are reserved in Excel for the R1C1 reference style.
You cannot use operator symbols (+, –, *, /, <, >, &) in range names. The only symbols valid in range names are the period (.), question mark (?), underscore (_), and backslash (\) symbols, as long as they are not used as the first character of the name.
Avoid using names that Excel uses internally, for example, Print_Area. Although Excel allows this name, using it can cause name conflict errors in the workbook. Other names to avoid are Auto_Activate, Auto_Close, Auto_Deactivate, Auto_Open, Consolidate_Area, Criteria, Data_Form, Database, Extract, FilterDatabase, Print_Titles, Recorder, and Sheet_Title.
The maximum length for a range name is 255 characters. That being said, you should avoid very long range names in general. Remember that the purpose of a range name is to provide a meaningful, easy-to-remember name that you can easily type into a formula.
Working with the Name Box
The Name Box, found to the left of the Formula Bar, offers a couple of handy features for working with named ranges. You can click the drop-down selector in the Name Box to see all the named ranges in your workbook (see Figure 1-8). Clicking any of the named ranges in the list automatically selects that range.
Figure 1-8: Use the Name Box to view and navigate to any named range within the workbook.
The Name Box also serves as a faster way to create a named range. To create a named range with the Name Box, first select the cell or range you want to name. Next, enter a valid name directly into the Name Box. Press the Enter key to create the name.
Tip: The Name Box is resizable. If you have a name that is too long for the Name Box, simply move your mouse cursor over the right edge of the Name Box until it turns into a horizontal arrow. When your cursor becomes a horizontal arrow, click and drag to widen the Name Box.
Common Mathematical Operations
Most Excel analysts working in the corporate world are asked to perform mathematical operations that provide insight into key operational metrics. Calculations such as percent of totals, variance to budget, and running totals are the cornerstone of any basic business analysis. In this chapter, you explore 12 mathematical operations commonly used in the world of business analytics.
Formula 1: Calculating Percent of Goal
When someone asks you to calculate a percent of goal, she is simply saying to compare actual performance to a stated goal. The math involved in this calculation is simple: Divide the goal by the actual. This gives you a percentage value that represents how much of the goal has been achieved. For instance, if your goal is to sell 100 widgets, and you sell 80, your percent of goal is 80 percent (80/100).
Note: Percent of Goal can also be referred to as percent of budget or percent of forecast; it all means the same thing.
In Figure 2-1, you see a list of regions with a column for goals and a column for actuals. Note that the formula in cell E5 simply divides the value in the Actual column by the value in the Goal column.
=D5/C5
Figure 2-1: Calculating the percent of goal.
How it works
There isn’t much to this formula. You’re simply using cell references to divide one value by another. You just enter the formula one time in the first row (cell E5 in this case) and then copy that formula down to every other row in your table.
Alternative: Using a common goal
If you need to compare actuals to a common goal, you can set up a model like the one shown in Figure 2-2. In this model, each region does not have its own goal. Instead, you’re comparing the values in the Actual column to a single goal found in cell B3.
=C6/$B$3
Figure 2-2: Calculating the percent of goal using a common goal.
Note that the cell reference to common goal is entered as an absolute reference ($B$3). Using the dollar symbols locks the reference to the goal in place, ensuring that the cell reference pointing to your common goal does not adjust as you copy the formula down.
Cross Ref: For more information on absolute and relative cell references.
Formula 2: Calculating Percent Variance
A variance is an indicator of the difference between one number and another. To understand this, imagine that you sold 120 widgets one day, and on the next day, you sold 150. The difference in sales in actual terms is easy to see; you sold 30 more widgets. Subtracting 120 widgets from 150 widgets gives you a unit variance of +30.
So what is a percent variance? This is essentially the percentage difference between the benchmark number (120) and the new number (150). You calculate the percent variance by subtracting the benchmark number from the new number and then dividing that result by the benchmark number. In this example, the calculation looks like this: (150-120)/120 = 25%. The Percent variance tells you that you sold 25 percent more widgets than yesterday.
Figure 2-3 demonstrates how to translate this into a formula. The formula in E4 calculates the percent variance between current year sales and previous year sales.
=(D4-C4)/C4
Most Excel analysts working in the corporate world are asked to perform mathematical operations that provide insight into key operational metrics. Calculations such as percent of totals, variance to budget, and running totals are the cornerstone of any basic business analysis. In this chapter, you explore 12 mathematical operations commonly used in the world of business analytics.
Formula 1: Calculating Percent of Goal
When someone asks you to calculate a percent of goal, she is simply saying to compare actual performance to a stated goal. The math involved in this calculation is simple: Divide the goal by the actual. This gives you a percentage value that represents how much of the goal has been achieved. For instance, if your goal is to sell 100 widgets, and you sell 80, your percent of goal is 80 percent (80/100).
Note: Percent of Goal can also be referred to as percent of budget or percent of forecast; it all means the same thing.
In Figure 2-1, you see a list of regions with a column for goals and a column for actuals. Note that the formula in cell E5 simply divides the value in the Actual column by the value in the Goal column.
=D5/C5
Figure 2-1: Calculating the percent of goal.
How it works
There isn’t much to this formula. You’re simply using cell references to divide one value by another. You just enter the formula one time in the first row (cell E5 in this case) and then copy that formula down to every other row in your table.
Alternative: Using a common goal
If you need to compare actuals to a common goal, you can set up a model like the one shown in Figure 2-2. In this model, each region does not have its own goal. Instead, you’re comparing the values in the Actual column to a single goal found in cell B3.
=C6/$B$3
Figure 2-2: Calculating the percent of goal using a common goal.
Note that the cell reference to common goal is entered as an absolute reference ($B$3). Using the dollar symbols locks the reference to the goal in place, ensuring that the cell reference pointing to your common goal does not adjust as you copy the formula down.
Cross Ref: For more information on absolute and relative cell references.
Formula 2: Calculating Percent Variance
A variance is an indicator of the difference between one number and another. To understand this, imagine that you sold 120 widgets one day, and on the next day, you sold 150. The difference in sales in actual terms is easy to see; you sold 30 more widgets. Subtracting 120 widgets from 150 widgets gives you a unit variance of +30.
So what is a percent variance? This is essentially the percentage difference between the benchmark number (120) and the new number (150). You calculate the percent variance by subtracting the benchmark number from the new number and then dividing that result by the benchmark number. In this example, the calculation looks like this: (150-120)/120 = 25%. The Percent variance tells you that you sold 25 percent more widgets than yesterday.
Figure 2-3 demonstrates how to translate this into a formula. The formula in E4 calculates the percent variance between current year sales and previous year sales.
=(D4-C4)/C4
How it works
The one thing to note about this formula is the use of parentheses. By default, Excel’s order of operations states that division must be done before subtraction. But if you let that happen, you would get an erroneous result. Wrapping the first part of the formula in parentheses ensures that Excel performs the subtraction before the division.
You can simply enter the formula one time in the first row (cell E4 in this case) and then copy that formula down to every other row in your table.
Cross Ref: for more information on the order of operator precedence.
Alternative: Simplified percent variance calculation
An alternative formula for calculating percent variance is to simply divide the current year sales by the previous year sales and then subtract 1. Because Excel performs division operations before subtraction, you don’t have to use parentheses with this alternative formula.
=D4/C4-1
Formula 3: Calculating Percent Variance withNegative Values
In the previous section, “Formula 2: Calculating Percent Variance,” you discovered how to calculate a percent variance. That formula works beautifully in most cases. However, when the benchmark value is a negative value, the formula breaks down.
For example, imagine that you’re starting a business and expect to take a loss the first year. So you give yourself a budget of negative $10,000. Now imagine that after your first year, you actually made money, earning $12,000. Calculating the percent variance between your actual revenue and budgeted revenue would give you -220%. You can try it on a calculator. 12,000 minus -10,000 divided by -10,000 equals -220%.
How can you say that your percent variance is -220% when you clearly made money? Well, the problem is that when your benchmark value is a negative number, the math inverts the results, causing numbers to look wacky. This is a real problem in the corporate world where budgets can often be negative values.
The fix is to leverage the ABS function to negate the negative benchmark value:
=(C4-B4)/ABS(B4)
Figure 2-4 uses this formula in cell E4, illustrating the different results you get when using the standard percent variance formula and the improved percent variance formula.
Figure 2-4: Using the ABS function gives you an accurate percent variance when dealing with negative values.
How it works
Excel’s ABS function returns the absolute value for any number you pass to it. Entering =ABS(-100) into cell A1 would return 100. The ABS function essentially makes any number a non-negative number. Using ABS in this formula negates the effect of the negative benchmark (the negative 10,000 budget in the example) and returns the correct percent variance.
Note: You can safely use this formula for all your percent variance needs; it works with any combination of positive and negative numbers.
Formula 4: Calculating a Percent Distribution
Percent distribution is a measure of how a metric (such as total revenue) is distributed among the component parts that make up the total. As you can see in Figure 2-5, the calculation is relatively simple. You divide each component part by the total. This example has a cell that contains Total revenue (cell C9). You then divide each region’s revenue by the total to get a percent distribution for each region.
Figure 2-5: Calculating a percent distribution of revenue across regions.
How it works
This formula doesn’t have a lot to it. You’re simply using cell references to divide each component value by the total. The one thing to note is that the cell reference to the Total is entered as an absolute reference ($C$9). Using the dollar symbols locks the reference in place, ensuring that the cell reference pointing to Total does not adjust as you copy the formula down.
Cross Ref: For more information on absolute and relative cell references.
The one thing to note about this formula is the use of parentheses. By default, Excel’s order of operations states that division must be done before subtraction. But if you let that happen, you would get an erroneous result. Wrapping the first part of the formula in parentheses ensures that Excel performs the subtraction before the division.
You can simply enter the formula one time in the first row (cell E4 in this case) and then copy that formula down to every other row in your table.
Cross Ref: for more information on the order of operator precedence.
Alternative: Simplified percent variance calculation
An alternative formula for calculating percent variance is to simply divide the current year sales by the previous year sales and then subtract 1. Because Excel performs division operations before subtraction, you don’t have to use parentheses with this alternative formula.
=D4/C4-1
Formula 3: Calculating Percent Variance withNegative Values
In the previous section, “Formula 2: Calculating Percent Variance,” you discovered how to calculate a percent variance. That formula works beautifully in most cases. However, when the benchmark value is a negative value, the formula breaks down.
For example, imagine that you’re starting a business and expect to take a loss the first year. So you give yourself a budget of negative $10,000. Now imagine that after your first year, you actually made money, earning $12,000. Calculating the percent variance between your actual revenue and budgeted revenue would give you -220%. You can try it on a calculator. 12,000 minus -10,000 divided by -10,000 equals -220%.
How can you say that your percent variance is -220% when you clearly made money? Well, the problem is that when your benchmark value is a negative number, the math inverts the results, causing numbers to look wacky. This is a real problem in the corporate world where budgets can often be negative values.
The fix is to leverage the ABS function to negate the negative benchmark value:
=(C4-B4)/ABS(B4)
Figure 2-4 uses this formula in cell E4, illustrating the different results you get when using the standard percent variance formula and the improved percent variance formula.
Figure 2-4: Using the ABS function gives you an accurate percent variance when dealing with negative values.
How it works
Excel’s ABS function returns the absolute value for any number you pass to it. Entering =ABS(-100) into cell A1 would return 100. The ABS function essentially makes any number a non-negative number. Using ABS in this formula negates the effect of the negative benchmark (the negative 10,000 budget in the example) and returns the correct percent variance.
Note: You can safely use this formula for all your percent variance needs; it works with any combination of positive and negative numbers.
Formula 4: Calculating a Percent Distribution
Percent distribution is a measure of how a metric (such as total revenue) is distributed among the component parts that make up the total. As you can see in Figure 2-5, the calculation is relatively simple. You divide each component part by the total. This example has a cell that contains Total revenue (cell C9). You then divide each region’s revenue by the total to get a percent distribution for each region.
Figure 2-5: Calculating a percent distribution of revenue across regions.
How it works
This formula doesn’t have a lot to it. You’re simply using cell references to divide each component value by the total. The one thing to note is that the cell reference to the Total is entered as an absolute reference ($C$9). Using the dollar symbols locks the reference in place, ensuring that the cell reference pointing to Total does not adjust as you copy the formula down.
Cross Ref: For more information on absolute and relative cell references.
Alternative: Percent distribution without a edicated Total cell
You don’t have to dedicate a separate cell to an actual Total value. You can simply calculate Total on the fly within the percent distribution formula. Figure 2-6 demonstrates how you can use the SUM function in place of a cell dedicated to holding a Total. The SUM function adds together any numbers you pass to it.
Again, note the use of absolute references in the SUM function. Using absolute references ensures that the SUM range stays locked as you copy the formula down:
=C3/SUM($C$3:$C$6)
Figure 2-6: Calculating percent distribution with the SUM function.
Formula 5: Calculating a Running Total
Some organizations like to see a running total as a mechanism to analyze the changes in a metric as a period of time progresses. Figure 2-7 illustrates a running total of units sold for January through December. The formula used in cell D3 is copied down for each month:
=SUM($C$3:C3)
Figure 2-7: Calculating a running total.
How it works
In this formula, you use the SUM function to add all the units from cell C3 to the current row. The trick to this formula is the absolute reference ($C$3). Placing an absolute reference in the reference for the first value of the year locks that value down. Locking the value down ensures that as the formula is copied down, the SUM function always captures and adds the units from the very first value to the value on the current row.
Cross Ref: for more information on absolute and relative cell references.
Formula 6: Applying a Percent Increase or Decrease to Values
A common task for an Excel analyst is to apply a percentage increase or decrease to a given number. For instance, when applying a price increase to a product, you would typically raise the original price by a certain percent. When giving a customer a discount, you would decrease that customer’s rate by a certain percent.
Figure 2-8 illustrates how to apply a percent increase and decrease using a simple formula. In cell E5, you apply a 10 percent price increase to Product A. In Cell E9, you give a 20 percent discount to Customer A.
Figure 2-8: Applying a percent increase and decrease using a simple formula.
How it works
To increase a number by a percentage amount, multiply the original amount by 1+ the percent of increase. In the example in Figure 2-8, Product A is getting a 10 percent increase. So you first add 1 to the 10 percent, which gives you 110 percent. You then multiply the original price of 100 by 110 percent. This calculates to the new price of 110.
To decrease a number by a percentage amount, multiply the original amount by 1- the percent of increase. In the example in Figure 2-8, Customer A is getting a 20 percent discount. So you first subtract 20 percent from 1, which gives you 80 percent. You then multiply the original 1,000 cost per service by 80 percent. This calculates to the new rate of 800.
Note the use of parentheses in the formulas. By default, Excel’s order of operations states that multiplication must be done before addition or subtraction. But if you let that happen, you would get an erroneous result. Wrapping the second part of the formula in parentheses ensures that Excel performs the multiplication last.
Cross Ref: For more information on the order of operator precedence.
You don’t have to dedicate a separate cell to an actual Total value. You can simply calculate Total on the fly within the percent distribution formula. Figure 2-6 demonstrates how you can use the SUM function in place of a cell dedicated to holding a Total. The SUM function adds together any numbers you pass to it.
Again, note the use of absolute references in the SUM function. Using absolute references ensures that the SUM range stays locked as you copy the formula down:
=C3/SUM($C$3:$C$6)
Figure 2-6: Calculating percent distribution with the SUM function.
Formula 5: Calculating a Running Total
Some organizations like to see a running total as a mechanism to analyze the changes in a metric as a period of time progresses. Figure 2-7 illustrates a running total of units sold for January through December. The formula used in cell D3 is copied down for each month:
=SUM($C$3:C3)
Figure 2-7: Calculating a running total.
How it works
In this formula, you use the SUM function to add all the units from cell C3 to the current row. The trick to this formula is the absolute reference ($C$3). Placing an absolute reference in the reference for the first value of the year locks that value down. Locking the value down ensures that as the formula is copied down, the SUM function always captures and adds the units from the very first value to the value on the current row.
Cross Ref: for more information on absolute and relative cell references.
Formula 6: Applying a Percent Increase or Decrease to Values
A common task for an Excel analyst is to apply a percentage increase or decrease to a given number. For instance, when applying a price increase to a product, you would typically raise the original price by a certain percent. When giving a customer a discount, you would decrease that customer’s rate by a certain percent.
Figure 2-8 illustrates how to apply a percent increase and decrease using a simple formula. In cell E5, you apply a 10 percent price increase to Product A. In Cell E9, you give a 20 percent discount to Customer A.
Figure 2-8: Applying a percent increase and decrease using a simple formula.
How it works
To increase a number by a percentage amount, multiply the original amount by 1+ the percent of increase. In the example in Figure 2-8, Product A is getting a 10 percent increase. So you first add 1 to the 10 percent, which gives you 110 percent. You then multiply the original price of 100 by 110 percent. This calculates to the new price of 110.
To decrease a number by a percentage amount, multiply the original amount by 1- the percent of increase. In the example in Figure 2-8, Customer A is getting a 20 percent discount. So you first subtract 20 percent from 1, which gives you 80 percent. You then multiply the original 1,000 cost per service by 80 percent. This calculates to the new rate of 800.
Note the use of parentheses in the formulas. By default, Excel’s order of operations states that multiplication must be done before addition or subtraction. But if you let that happen, you would get an erroneous result. Wrapping the second part of the formula in parentheses ensures that Excel performs the multiplication last.
Cross Ref: For more information on the order of operator precedence.
The account of the user that created this channel has been inactive for the last 11 months. If the account of the creator remains inactive in the next 30 days, it will self-destruct and the channel will lose its creator.
The account of the user that created this channel has been inactive for the last 11 months. If the account of the creator remains inactive in the next 29 days, it will self-destruct and the channel will lose its creator.
The account of the user that created this channel has been inactive for the last 11 months. If the account of the creator remains inactive in the next 28 days, it will self-destruct and the channel will lose its creator.
The account of the user that created this channel has been inactive for the last 11 months. If the account of the creator remains inactive in the next 28 days, it will self-destruct and the channel will lose its creator.
The account of the user that created this channel has been inactive for the last 11 months. If the account of the creator remains inactive in the next 20 days, it will self-destruct and the channel will lose its creator.
The account of the user that created this channel has been inactive for the last 11 months. If the account of the creator remains inactive in the next 19 days, it will self-destruct and the channel will lose its creator.
The account of the user that created this channel has been inactive for the last 11 months. If the account of the creator remains inactive in the next 18 days, it will self-destruct and the channel will lose its creator.
The account of the user that created this channel has been inactive for the last 11 months. If the account of the creator remains inactive in the next 17 days, it will self-destruct and the channel will lose its creator.
The account of the user that created this channel has been inactive for the last 11 months. If the account of the creator remains inactive in the next 10 days, it will self-destruct and the channel will lose its creator.
The account of the user that created this channel has been inactive for the last 11 months. If the account of the creator remains inactive in the next 9 days, it will self-destruct and the channel will lose its creator.
The account of the user that created this channel has been inactive for the last 11 months. If the account of the creator remains inactive in the next 8 days, it will self-destruct and the channel will lose its creator.
The account of the user that created this channel has been inactive for the last 11 months. If the account of the creator remains inactive in the next 7 days, it will self-destruct and the channel will lose its creator.