INTRODUCTION – Formulas and functions
An in-depth guide to excel’s formulas and functions-it’s the way into exploring these most powerful tools in excel for data analysis and how they can be used to interpret as well as manipulate data to make more informed decisions. This module illustrates by examples through several business situations, how Excel formulas and functions can be used to cater for even more operational efficiency, productivity increases, and operational potentials. At the end of this module, users are expected to have achieved a holistic comprehension of application and function in the analysis of data-driven business cases through Excel’s computational features.
Goals of learning
- Understand the correct grammar for formula calculation and function.
- Use the most common formulas and functions on a worksheet.
SELF-REVIEW: CALCULATING PROFIT AND MARGIN
In the exercise Calculating profit and margin, you helped Adventure Works to calculate their annual profits and margins using the dataset in the worksheet Jan-Dec in the Microsoft Workbook Revenue figures.
Your final worksheet should resemble the following screenshot:
Now it’s time to review your understanding of the tasks you completed by answering the questions that follow. Don’t forget that you can revisit the previous learning items to recap the process steps.
1. When you created the calculation in cell G4, you were able to use the double-click shortcut to Autofill copies of the formula into the cells underneath. What data did Excel use as a visual reference to determine when to stop copying the formula?
- The block of information in row 201.
- The block of information in column F. (CORRECT)
- The block of information in column K.
Absolutely! For the Autofill double-click trick to work, there should be in this column before or after the cursor a block of data for Excel to automatically fill in the rest of the data based on the pattern or information in the adjacent column.
2. In cell H4 you created a formula that worked out the total shipping cost. Cell P1 contained the $5 per item shipping charge. Column F contained multiple rows of entries. Which of the following was the correct syntax for the formula to ensure that it copied correctly when you used the Autofill shortcut?
- =$F$4*P1
- =F4*P1
- =F4*$P$1 (CORRECT)
That’s correct! The cell in P1 has only one figure. The dollar signs in front of the column letter and row number ($P$1) keep that formula fixed on the value in P1 regardless of copying it down the column. This reference is thus rendered as absolute and fixed. On the other side, in reference to F4, the dollar sign is not necessary because it is a relative reference that automatically takes care of changing according to changing the base cell above it as the references moves down to let formula really work for different numbers of items in column F.
3. In cell J4, you created a formula with parentheses to set a new retail price by adding numbers together and working out a percentage increase. What result did you get for the calculation?
- $224.66
- $333.24 (CORRECT)
- $222.16
That’s correct! You probably need to enclose parentheses around the sum, and Excel will add the two costs before multiplying them, giving the percentage rate. As a result, the sequence of events in the process has been changed and it eventually leads to a dissimilar end result. Another adjustment needs to be made for P1, which should change to $P$1. By doing this, the reference can be maintained without changing when copying the formula down the column. When referencing, it should be mentioned that not using the dollar signs might cause errors due to the shifting of references, even if the copying of the cells has been made without interfering with them.
4. You are creating a formula in a worksheet called Summary. This formula also includes a reference to cell H2 in a worksheet called Products. How will this reference appear in the formula?
- ProductsH2
- Products!H2 (CORRECT)
- H2
That’s correct! An example of using this separator can be seen when you reference a cell, for instance, cell A1, from a worksheet, for instance, named “Sales.” The spreadsheet formula would look like this: Sales!A1. In this case, “Sales” is the name of the worksheet and “A1” is the cell reference. The exclamation mark establishes an understanding within Excel software that the reference is within different worksheets rather than with the current one.
5. You are creating a calculation in your worksheet. You want Excel to subtract a figure held in C5 from a total held in H2 and then for the result to be divided by four. Which formula will calculate this correctly?
- =H2-C5/4
- =(H2-C5)/4 (CORRECT)
- =C5-H2/4
That’s correct! The parentheses with the minus sign are forming process steps for this equation, which means that the subtraction will happen first by recording; subsequently, the result is to be put over four. To ensure the subtraction comes before it—and thus the right result—it is true that if these brackets were missing, the division that would have occurred first would give an incorrect answer.
6. A Microsoft Excel worksheet contains the following formula:
=A2*B3/C2+D4
Which operator in this calculation will Excel process first?
- Addition
- Multiplication (CORRECT)
- Division
That’s correct! The inclusion of the parentheses eliminates all ambiguity as to which step in the calculation should be performed first, as PEMDAS dictates that the subtraction should occur first. Once the subtraction is completed, the remaining number will be divided by four, ensuring subtraction is executed before division and therefore will give the correct answer. When the answer included in parentheses is multiplied or divided prior to subtraction, the outcome following such calculation would be incorrect.
KNOWLEDGE CHECK: FORMULAS IN EXCEL
1. You are creating a formula in cell C2 in your worksheet. The calculation only needs to appear in cell C2. You have typed the following formulas:
A3*D15/45*F17
However, there is an error in the syntax of this formula. What part of the formula is missing?
- Parentheses
- Dollar Signs
- An Equals Sign (CORRECT)
Feedback: It certainly does. So, any calculation you make on Excel starts with an “=” sign, which shows the cell is a formula instead of just plain, regular text. On input that does not have the = sign, Excel understands it as plain text rather than as computation. To say, for example, to add two numbers, you would type into the formula bar: =A1 + B1.
2. Cell D2 of your worksheet contains the weekly sales total. Cell D7 contains the overall monthly sales. You need cell E2 to show what percentage the week one total is of the overall monthly total. You’ve already applied the percentage format to E2. Which of the following options is the correct syntax for the formula?
- =D2/D7 (CORRECT)
- =D7/D2
- =D2/D7*100
That’s correct. The weekly gross sales is about dividing the subtotal of sales over the year (from all the months) by the overall total. It would automatically present a Percent style cell, so that Excel would multiply a result by a hundred, thereby displaying it as a percentage. For instance, let us say that a weekly total is 500, but the total for the entire month is 2000. =A1/B1 (Where A1 is the weekly total and B1 is the monthly total) would return to a formula of 0.25, and this would be displayed as 25% on Excel, due to the percent format.
3. True or False: When Excel processes the following formula, it will first multiply cell A1 by cell C3 before it processes the addition.
=(B15+D45)-(A1*C3)
- True
- False (CORRECT)
That’s correct! The two parenthesis pairs are symbols that control how Excel’s calculation is performed: it provides an order for the operations so that they can be worked through step by step. When there are numerous parentheses in sequence, Excel processes the parentheses from left to right, thereby executing operations that are within parentheses first and in the order in which they are written. For instance, within =(2+3)*(4+5) Excel will first add 2 and 3 (from left to right) and add 4 and 5; then it will multiply the results together.
4. You have created the following formula in your worksheet:
=D4+$C$6-E4+F6
You use the Autofill feature to copy the formula down the column. Which of the cell references will update with new row numbers?
- C6, E4, F6
- D4, C6, E4
- D4, E4, F6 (CORRECT)
That’s correct. As far as the formula is concerned, these are references to cells that do not require dollar signs. Relative references will ensure that the row numbers update automatically when you copy the formula to a new cell. This is what truly makes dynamic referencing, with each referred row within the formula adjusted to an appropriate one in the column..
5. What mathematical sign does Excel process last when calculating the results of the following formula?
=D4+C3/N5*B3
- The plus sign. (CORRECT)
- The Division sign.
- The Multiplication sign.
That’s correct! Mathematically, it is more frequent to place a multiplication problem before the subtraction problem. So the answer is multiplication. A similar phenomenon occurs with division and addition. Division and multiplication are both treated before addition and subtraction because when both multiplication and division take place in a formula, Excel carries out multiplication and division from left to right, meaning they hold the same value. Hence, the formula 10 + 6 / 2 * 3 will solve as follows: Excel will allegedly simplify the 6 / 2 and multiply it by 3, and finally add 10.
SELF-REVIEW: PREPARING A MONTHLY SALES REPORT
In the ungraded exercise, you completed a series of tasks to prepare a monthly sales report for Adventure Works.
You first downloaded the file Monthly sales report.xlsx. You then created a series of calculations to determine the total sales amount for the month of April.
A screenshot of the worksheet is included for reference.
Now it’s time to review your understanding of the tasks you complete by answering the questions that follow. Don’t forget that you can refer to previous lesson items to recap your process steps.
1. Cell C34 in your worksheet is blank and cell C35 contains a formula. The cursor is on C36. If you use the AutoSum shortcut to add a SUM formula, which cell range does Excel automatically suggest as the argument for the function?
- C34 (CORRECT)
- C36
- C35
That’s correct! Above the active cell position, Excel scans for potential arguments for the SUM function formula. As an illustration, it includes the value in cell C35 above the cursor while leaving out the empty cell that happens to be above it. Excel automatically detects contiguous data ranges for the SUM function until such time as blank cells or interruptions in data set in.
2. True or False: When you created the Total Revenue formula in C35, the results appeared as dollar amounts because the Accounting format had been applied to the cell in advance.
- True
- False (CORRECT)
That’s correct! When the workbook opened, cell C35 was indeed still general in format . The format got changed on C35 automatically through the formula referring it to the cell range E4 of E33. All those cells with Accounting format format become automatically applied to one single result in C35 .
3. When you created the formula in C39 to count how many days there were in the month, what answer did the formula produce?
- 0
- 31
- 30 (CORRECT)
That’s correct! / is the character used to isolate the numeric components of a date; therefore, of course, Excel automatically recognizes the date as a number. Consequently, the COUNT function formula that should be written will count the dates correctly.
4. You need to access the full range of functions available in Excel. Which ribbon do you need to select to access the full library of functions?
- The Formula ribbon. (CORRECT)
- The Data ribbon.
- The Home ribbon.
That’s correct! Category headings are printed under the name of a function. There are generally several functions in a category by virtue of the total number of functions alone.
5. You need to access the Insert function feature to create a function formula. What actions can you take to access this feature? Select all that apply:
- Use the Insert function choice on the Formulas ribbon. (CORRECT)
- Use the FX symbol to the left of the Formula bar. (CORRECT)
- Use the Name box beside the Formula bar.
Just as you rightly guessed! The function Insert key is used for opening an Add Function box.
Absolutely right! To open the function key Insert box, choose the FX symbol at the upper left of the Formula bar.
KNOWLEDGE CHECK: GETTING STARTED WITH FUNCTIONS
1. Which of the following items are part of a function formula? Select all that apply:
- Parentheses (CORRECT)
- An equals sign (CORRECT)
- Periods
- The function name (CORRECT)
Every element which the function operates on needs to be enclosed in parentheses not only in an equation but also in a range.
Like all the other formulae, a function formula should be preceded by an equals sign so that Excel recognizes it as a formula.
In a function formula, there should be a single word naming the function.
2. You need to know how many cells in a cell range contain entries. The cells with entries contain a mixture of text and numeric entries. Which function should you use in this situation?
- COUNTA (CORRECT)
- COUNT
- COUNTBLANK
That’s correct! The COUNTA command counts every cell, digits and letters included, that has data in it.
3. Three cells in your worksheet contain numbers. Cell D7 holds the number 10, D8 contains the number 20, and D9 contains the number 30. What is the result of the following formula when added to cell D10?
- =MAX(D7:D8)
- 10
- 30
- 20 (CORRECT)
That’s correct! It refers to D7:D8 in its formula. This reference outputs two values of which 20 is the largest value and which is the right answer.
4. Cells B1 to B3 in your worksheet contain numbers. Cell B4 has a piece of text. You add an AVERAGE function formula to cell B5, which works out the average of cells B1 to B4. How will Excel work out the average?
- Total the numbers in B1 to B3 and divide by 4.
- Total the numbers in B1 to B3 and divide by 5.
- Total the numbers in B1 to B3 and divide by 3. (CORRECT)
That’s correct! At its current stage, Excel will ignore the storage of a text into the cell when carrying out the average. It instead sums up the three numbers in Cells B1 to B3 and then divides their sum by 3, thus providing what one may term an instantaneous (e.g. “point-in-time”) average.
5. Cell E4 in your worksheet contains the value 300, cell E5 the value 300, and cell E6 the value 0. What is the correct answer to the following formula when added to your worksheet?
=AVERAGE(E4:E6)
- 300
- 200
- 100 (CORRECT)
That’s correct! All three cells contain numbers, so Excel first adds together the values in cells E4 and E5 and then divides the result by three.
MODULE QUIZ: FORMULAS AND FUNCTIONS
1. You need to add a new figure in cell H52 of a workbook. However, you accidentally begin typing the number in cell H51, which already contains data. What key can you press to cancel your entry?
- The Enter key.
- The F4 Key.
- The ESC key. (CORRECT)
That’s correct. If you poke around or write in error in a cell, press the ESC key to get out of edit mode and bail out.
2. True or False: The following formula automatically recalculates and the result updates if new values are entered in any of the referenced cells.
=A2+F3-H3
- True (CORRECT)
- False
That’s correct! A reference from a cell can update automatically whenever the contents of the cell being referenced changes.
3. Row one of your spreadsheet contains the following formula:
=A1*C1/$B$1
If you use the Autofill feature to copy the formula down to the next row, what will the formula in row two be?
- =A2*C2/$B$1 (CORRECT)
- =A2*C2/$B$2
- =A2*C1/$B$1
That’s correct. The dollar signs in the reference lock the row numbers on B1 cell reference when copying the formula. It keeps active the copied references on next-row copying while avoiding formula changes.
4. You have created a formula in your worksheet which reads:
=(300+30)/(10-7)
What would the answer be?
- 110 (CORRECT)
- 296
- 330
That’s correct! Understanding the modified BOD test. The higher the value, the greater the tendency of a substance to absorb oxygen. For volatile substances, if the molecules are not covalently bonded, they evaporate. Any modifications to a chemical do not change the properties of coated substances.
5. Column B of your worksheet contains stock item prices. In cell C2 you need a formula to calculate the discount on the current stock price in B2 using the 10% discount rate in cell F2. You also need to copy this formula down column B to apply it to the other stock items listed. Which formula would calculate the discount and allow for the formula to be copied?
- =B2*$F$2 (CORRECT)
- =B2/$F$2
- =B2*F2
That’s correct! The value in cell F2 contains a percentage symbol. So, multiplying by this value calculates 10%. The dollar signs on the F2 reference make sure that it remains constant when the formula is copied down column B.
6. True or False: You are using the Insert Function feature to add a formula to your worksheet. You’ve switched to the category Math & Trig to find a function. The list of functions is organized in the order that they were recently used.
- True
- False (CORRECT)
That’s correct! The functions are listed alphabetically in all the categories except for Most Recently Used.
7. As you type a function formula into a cell, a help message appears to remind you of the arguments required. How does the message display the optional arguments?
- The arguments are contained in square brackets. (CORRECT)
- The arguments are in bold.
- The arguments are italicized.
That’s correct! Floating help will help to communicate that necessary data to any provided executable. Italic elements are a part of an executable instruction that is required; all optional ones are in square brackets.
8. According to the order of precedence, which mathematical operators would Excel automatically assign a higher priority to than a minus symbol? Select all that apply:
- Addition
- Division (CORRECT)
- Multiplication (CORRECT)
Division has a shorter precedence than multiplication and addition in an order-table to create a representation of subtraction from that.
Multiplication has less precedence than division and higher precedence compared to addition.
9. Cell C7 in your worksheet contains the value 300, and cell A4 has the entry 12. Which of the following calculations would correctly calculate 12% of the value in C7? Both A4 and the formula cell have the General format applied. Select all that apply.
- =C7/100*12%
- =C7/100*A4 (CORRECT)
- =C7/100*12 (CORRECT)
Right. In cell A4, the standard contains a value. The one percent value would be obtained if C7 were divided by 100 to produce a result that could further be multiplied by the value in A4 to acquire twelve percent.
That’s right. We applied the general format in the formula cell. From C7, divide by 100 to get a one percent value, then multiply by 12 to get a twelve percent value.
10. Your team achieved sales of $35,000 last month and $45,000 this month. Which of the following calculations would work out the percentage they had increased their sales by?
- =(45000-35000)/35000 (CORRECT)
- =(45000-35000)/45000
- =(35000-45000)/35000
That’s correct. To calculate the difference, subtract last month income and the present month income. Divide the result by the last month’s and get a percentage.
11. Cell E7 in your worksheet contains a value of 0. F7 contains a value of 100. Cell G7 contains a value of 200, and cell H7 contains a piece of text. What is the correct answer for the following formula?
=COUNTA(E7:H7)
- 2
- 3
- 4 (CORRECT)
COUNTA includes a cell in the count if the cell contains any content, whether it is text or those of numeric. All cells from E7 to H7 contain content; hence, then result of the formula becomes four.
12. Cell A1 of your spreadsheet contains the value 10. Cell B1 contains the value 5, and cell C1 contains the value 2. The formula you created in cell D1 reads:
=A1*B1+$C$1
Row 2 of your spreadsheet is completely empty. If you were to copy the formula down to cell D2 what would the formula result be?
- 52
- 50
- 2 (CORRECT)
You are got absolutely correct! If you put dollar signs in front of the C1 reference in the formula, the effect is that the formula will always use the value in cell C1. This way, the value in C1 will still be used when adding together the formula allowed procedure.
13. What mathematical symbol is processed first in the following formula?
=((A3+H4)/G7)*5
- The Division symbol.
- The Addition Symbol. (CORRECT)
- The Multiplication Symbol.
That’s right! Another rule that is followed by Excel is that, within parentheses, this rule is respected when we have certain cases: the formula of the matrix includes parentheses in parentheses. Here, the innermost parenthesis itself contains the operator of addition.
14. You are creating a worksheet to track team performance. You know that the team increased their sales in May. The team’s April sales figures are in cell C5. Their sales figures for May are in cell D5. Which one of the following calculations would show you the percentage increase?
- =(D5-C5)/C5 (CORRECT)
- =(D5-C5)/D5
- =(C5-D5)/D5
That’s correct. Subtract the new value of May sales from that of April to achieve this real difference between the two sales. Next, divide by April and note the increase percentage.
15. Cell F7 contains a value of 100. Cell G7 has a value of 200, and cell H7 is empty. What is the correct answer for the following formula?
=AVERAGE(F7:H7)
- 100
- 150 (CORRECT)
- 200
That’s correct! Only two of the three cells referenced in the formula contain numbers, so Excel adds the two values in F7 and G7 together and then divides the result by two.
16. What information must be added to the Number 1 and Number 2 boxes when working with the Function Arguments dialog to add a SUM function formula using the Insert Function wizard?
- The name of the function.
- The function parentheses.
- The function arguments. (CORRECT)
That’s correct! When one sums frequently one uses cell references from the range required; these cell references are designated as arguments.
17. You are using the Insert Function wizard to add a formula to your worksheet. When the Insert Function dialog opens, what Function category list is displayed?
- Most Recently Used (CORRECT)
- All
- Math & Trig
That’s correct! Recent Features Used are prioritized in the list so that you can save your time. In this list are the capabilities that you use most recently in your worksheet.
18. The formulas in your worksheet are not recalculating. You discover that this is because the Automatic Calculation feature is turned off. Which ribbon can you access this feature from to turn it back on?
- The Home Ribbon.
- The Formula Ribbon. (CORRECT)
- The Data Ribbon.
That’s correct! The list for calculating the different options is on the right side of the formula tab, under the calculation group. Automatic calculation has a checkmark at the top of the list. If the word Automatic doesn’t have a check mark next to it, then the Automatic calculation is turned off.
19. True or False: You are working with the Number 1 and Number 2 boxes in the Function Arguments dialog in the Insert Function wizard. The Number 2 entry is not bolded, which means that it’s optional.
- Normalize data, automate data, and analyze data
- Collect and aggregate data, normalize data, and analyze data (CORRECT)
- Collect and aggregate data, analyze data, normalize data
- Collect and aggregate data, normalize data, and automate data
The three steps of the SIEM process are: collecting and aggregating data, normalizing data, and analyzing data.
20. Cell A4 in your worksheet contains the value 10. Cell A5 contains the value 100. Cell A6 contains the value 2. In cell A7 you have created the following formula:
=10*100/2
If you change the value in cell A6 to 4, then what would the result of this formula be?
- 750
- 500 (CORRECT)
- 250
That’s correct! This formula contains fixed values instead of cell references in that it will not recalculate or generate a new result if content in A4, A5 or A6 is changed.
21. As you type a function formula into a cell, a help message appears to remind you of the arguments required. How does the message display these required arguments?
- The arguments are in bold. (CORRECT)
- The arguments are contained in square brackets.
- The arguments are italicized.
That’s correct! The floating hint message provides a useful reminder of what a function needs. Mandatory items are shown in bold, optional ones are enclosed by square brackets.
22. What function does Excel use when the Count Number choice is selected from the AutoSum drop-down list?
- COUNTBLANK
- COUNT (CORRECT)
- COUNTA
That’s correct. The COUNT function only counts those cells whose entries are numeric. To avoid confusion, this option in the drop-down list of AutoSum says “Count Numbers.”
CONCLUSION – Formulas and functions
Indeed, with this module, users are able to have their own deep dive for the much needed formulas and functions found on Excel. Data analytics is highlighted at this point in time- how to make sense through data structures and how the same information would mean anything in practice into businesses. When a user understands the tools well, he is able to manipulate data and also interpret data more accurately which will generate better decision making and therefore more effective operations, simultaneously enhancing the overall efficiency of whatever tasks they wanted to accomplish. Qualified personnel are fully aware of how to use Excel to maximum advantage, getting the best business results and improving productivity overall of their professionals.