Excel functions

Week 2: Working with formulas and functions.

Exercise 1: Getting started with formulas

2. Note that the formula to calculate the subtotal in cell E6 is missing:

3. Enter the formula “=B6+C6*D6” in cell E6, then press Enter:

4. Notice that the result, $21.99, does not accurately reflect the total value of the three items. According to the precedence of
the order of operations, the formula first calculates the multiplication of cell C6 * D6 (3 * 3 = 9), then adds the result to the
value of cell B6 (12.99 + 9 = 21.99). To correctly calculate the subtotal, insert parentheses around the operation you want to
calculate first, “=(B6+C6)*D6”, then press Enter:

5. Now that you have corrected the formula, select cell E6, press Ctrl + C to copy the formula, then select cells E7 through
E9 and press Ctrl + V to paste the formula:

6. Click Formulas → Show Formulas to show formulas rather than values in the cells that contain them. Note that
when you copied the formula to the cells below, the cell references automatically change relative to the cell that contains the
formula:

7. Now select cell F6, press Ctrl + C to copy the formula, then select cells F7 through F9 and press Ctrl + V to paste the
formula:
8. Click Formulas → Show Formulas to return to the default view of values rather than formulas:

9. Notice that the results in F7 through F9 are incorrect. This is because the formula in cell F6 uses a relative reference for
the discount value in cell B3. When you copy the formula to the cells below F6 the reference is automatically adjusted.
Select cell F7 to show the cell references in the formula. The reference to the discount amount has been adjusted to cell B4:

10. To correct this, select cell F6 and add a dollar sign ($) before the row reference to the discount amount. “=E6*B$3”:
11. Press Ctrl + C to copy the adjusted formula, select cells F7 through F9, then press Ctrl + V to paste the formula:

12. Click Formulas → Show Formulas. Note that the references to the subtotal cells have changed but the absolute
references to the discount amount are the same:

13. You can now click Formulas → Show Formulas to return to the default view. To finalize the workbook, select
cell G6, press Ctrl + C to copy the formula, then select cells G7 through G9 and press Ctrl + V to paste the formula:
14. Save your workbook as Activity 2-1 Complete. Close Microsoft 365 Excel to complete this activity.

Exercise 2: Using formulas and functions

2. First, we need to set up formulas to calculate the operating profit. Click cell F2:

3. Type an equal sign (=), then, click cell D2:

4. Type a minus sign (-) and click cell E2:

5. Press Enter to complete the formula:

6. We will complete the rest of the column in the next activity. For now, click cell H2 and repeat Steps 2 through 5 to create
a formula that subtracts depreciation from the operating profit, to calculate the net profit:

7. Now, let’s work on our scorecard. Click cell L2, where we want to calculate the average revenue:
8. Click the AutoSum arrow on the Home tab and click Average:

9. By default, Excel will select the values in the first part of the row, but this is not what we want. Click and drag to select
cells D2 to D20 instead:

10. Press Enter to complete the formula and calculate the results:

11. Cell L3 should now be selected. This is where we want to display the highest revenue. Let’s use a different method to
create this formula. To begin, click Formulas → Insert Function:
12. Ensure that the Most Recently Used category is selected. Then, click the MAX function from the list. Click OK
to insert it. If you do not see the MAX function in the Most Recently Used category, change to the Maths category
and select it from there.

13. Now, click and drag to select cells D2 to D20:

14. Click OK to complete the formula and calculate the results:

15. Now, use either the AutoSum command or the Insert Function command to calculate the lowest value in the
Expenses column with the MIN function:

16. Let’s double-check the first two values we calculated. Select cells D2 to D20:

17. Look at the average calculated in the status bar, and compare it to the average calculated using the function:

18. However, the MAX function is not shown here. Right-click anywhere on the Status bar, then click to select Maximum
in the Customize Status Bar dialog box:
19. Click anywhere in the Excel window outside of the Customize Status Bar dialog box. The dialog box will close, and you
will now see the Max value in the status bar, which should match the result of the Max function:

20. Save your workbook as Activity 2-2 Complete. Close Microsoft 365 Excel to complete this activity.

Exercise 3: Sort and filter data

2. Our first task is to sort the data by state. Select cells B2 to B20:
3. Now, click Home → Sort & Filter → Sort Ascending:

4. Select Expand the selection in the Sort Warning dialog box and click Sort:

5. Review the results:

6. Now we want to filter out the corporate data. Select any cell within the data and click Data → Filter:
7. Click the drop-down arrow in cell C1 (Office Type):

8. Click in the checkbox next to Corporate to deselect, then click OK:

9. Review the results, and note the AutoFilter icon in cell C1, the blue row numbers, and the double lines indicating the
hidden cells:
10. Save your workbook as Activity 2-5 Complete. Close Microsoft 365 Excel to complete this activity.

Exercise 4: Using range names in formulas

2. To create the first range name, use your cursor to select cells B4:B6:

3. Next, type “Prices” inside the Name Box. Press Enter:

4. The selected range now has “Prices” as a range name:

5. Now, let’s try another method to create another range name. First, use your cursor to select cells C4:C6:

6. Next, click Formulas → Define Name:

7. The New Name dialog box is now displayed. Ensure that “Quantity” appears inside the Name text box and that the Scope
drop-down menu is set to Workbook. Click OK:

8. The selected range now has “Quantity” as a range name:

9. You have one more range name to create. Use your cursor to select cells D3:D6:
10. Click Formulas → Create from Selection:

11. In the Create Names from Selection dialog box, ensure that the “Top row” checkbox is selected and click OK:

12. Next, you need to create a formula that will calculate the cost of the items (Quantity*Prices). Select cell D4:

13. Click inside the Formula Bar and type “=”:

14. Next, type “Prices” followed by an asterisk:

Note that because Prices is a range name, its text will appear blue in the Formula Bar and blue shading will appear around
that range of data on the worksheet.

15. Still inside the Formula Bar, type “Q” and then double-click the Quantity result from the small menu that appears:

16. The Quantity name now appears within the Formula Bar in red text, with its associated range shaded in red in the
worksheet:
17. Press Enter to apply the formula. You will see the results appear in cells D4 through D6:

(You may receive a “Formula Spilled” alert, indicating that the formula returned multiple values, so they were spilled into
the neighboring blank cells. Because each of the named ranges contains more than one value, Excel must predict the correct
calculation for each value.)

18. Save the current workbook as Activity 1-1 Complete and then close Microsoft 365 Excel to complete the exercise.

Exercise 5: Using specialized functions

2. First, click to select cell G3:
3. Next, click Formulas → Financial → PMT:

4. The Function Arguments dialog box appears. Within this dialog box you need to enter all of the arguments. As the interest
rate is stored in cell E3, type “E3” into the Rate text box:
5. As these are annual interest rates and the payments will be monthly, you need to divide this value by 12. Type “/12”
following the cell reference in the Rate text box:

6. The next argument is Nper, or the number of payment periods over the life of the loan. This information is contained in
cell D3, but it is provided in years. Because you need to enter it as months, type “D3*12” into the Nper text box:

7. The next argument is Pv, or present value. This is the amount of money that is being borrowed. This information is
contained in cell C3, so type “C3” into the Pv text box:
8. Leave the Fv (Future Value) argument field empty. This argument will default to 0, which is what we want. (This means
there will be no part of the loan left outstanding at the end of the payments.) We will also let the Type field default to 0,
meaning payments will be due at the end of the payment period. Click OK to create the function:

9. You now have a result in the cell G3. You will also see the PMT function in the Formula Bar:

10. Now it is time to enter this formula for the rest of the data rows. To do this, click cell G3 to make it active, and then drag
the AutoFill handle in the lower right corner of the cell down to G94:
11. Release the mouse button. You will see that the loan payments for each entry have been calculated:

12. Save the current workbook as Activity 1-2 Complete and then close Microsoft 365 Excel to complete this exercise.
 Exercise 6: Analysing data using text
functions
2. First you would like to find the first initial from the first name that is entered into column A. Click to select cell C2 and
type “=LEFT(A2)” into the Formula Bar:

3. Press the Enter key on your keyboard and you will see that the previously selected cell now displays the initial from the
first name (“J” in this case):

4. Next, you need to do the same thing and find the first initial from the last name that is entered into column B. Click to
select cell D2 and type “=LEFT(B2)” into the Formula Bar:

5. Press the Enter key on your keyboard and you will see that the previously selected cell now displays the initial from the
last name (“S” in this case):
6. Now you need to use the TEXTJOIN function to fill in the Full Initials column. Click to select the E2 cell and then type
“=TEXTJOIN(,TRUE,C2,D2)” into the Formula Bar:

7. Press the Enter key and you will see that the values from cells C2 and D2 have been combined to show the full initials:

8. Finally, you would like to automatically fill in the shipment method based on the number of characters that appear in the
invoice number. If the invoice has more than five characters, then it is designated a rush order and if it is five characters or
less, it is a standard order. Select H2:

9. Type “=IF(LEN(G2)>5, "RUSH", "STANDARD")” into the Formula Bar.

Note: The IF function will be covered in greater detail in the next lesson.

10. Press the Enter key on your keyboard and you will see that this particular order is a rush order because its invoice
number is more than five characters:
11. Use the Auto Fill feature to copy the formulas that you entered during this activity into the adjacent cells in row 3:

12. Save the current workbook as Activity 2-1 Complete and then close Microsoft 365 Excel to complete this exercise.

Exercise 7: Analysing data using logical

functions
2. First you need to fill in the ID column. This data is the first name and last name separated
by an underscore (_). While you can do this manually, the TEXTJOIN function is built for
exactly this type of work. Select cell C5, then, in the Formula Bar, type
“=TEXTJOIN(“_”,TRUE,A5,B5)”

3. Press Enter, to enter the formula, then select cell C5 again, hover your cursor over the
cell handle until your cursor icon turns into a small black cross:
4. Double click on the cell handle to automatically copy your formula down the
column, to cell C14:

5. Next, you need to determine which salespeople are going to receive a bonus. Bonuses are
dispensed when the representative exceeds their weekly sales goal, and if their call reports are
complete. For this purpose, you can use the AND function. Use your cursor to select cells
G5 on the worksheet:

6. Inside the Formula Bar, type “=AND(D5="Yes",E5>F5)” and then press Enter:
7. Because both logical tests are true, the formula returns the value of TRUE in cell G5. Now
drag the AutoFill handle at the bottom right of the cell down to cell G14:

8. All sales representatives who have met both conditions now show a value of TRUE in this
column. To add the bonus amount to column H, first select cells H5 through H14:

9. Inside the Formula Bar, type “=IF(G5:G14=TRUE,K2, 0)”. Because this is an array
function, press Ctrl + Shift + Enter:
10. The bonus information has now been calculated for all employees in this worksheet:

11. Save the current workbook as Activity 2-2 Complete and then close Microsoft 365 Excel
to complete this exercise.