1. When using formulas, always remember that they need to start with an. [Relative, Absolute, and Mixed References]

(1)

59 | Domain 4: Fill-in-the-Blanks Excel 2019 Project Workbook, First Edition

Fill-in-the-Blanks

Instructions: While watching Domain 4, fill in the missing words according to the information presented by the instructor.

[References are found in the brackets.]

Lesson 1

1. When using formulas, always remember that they need to start with an . [Relative, Absolute, and Mixed References]

2. In a formula, you can use the name of a range instead of its . [Use Named Ranges and Tables in Formulas]

3. cells do not count within an average, but do. [Use AVERAGE, SUM,

MIN, and MAX]

4. The MIN function can be found by clicking More Functions on the ribbon and then pointing to

. [Use AVERAGE, SUM, MIN, and MAX]

5. The function counts cells with either numbers or text, while the function only counts cells with numbers in them. [Use COUNT, COUNTA, and COUNTBLANK]

6. The IF function can be found under the drop-down button on the Formulas tab of the ribbon.

[Use the IF Function]

(2)

60 | Domain 4: Introduction to Formulas Excel 2019 Project Workbook, First Edition

=3x9

=144\12

=SUM(1,3*(1+4)

SUM()+5*6

Introduction to Formulas

To start a formula in a cell, begin the cell with an equal sign. The formula assigned to a cell will be displayed in the Formula Bar above the worksheet or in the cell if Show Formulas is enabled. Changes to a formula can be made in the Formula Bar after selecting a cell or in the cell itself after double-clicking the cell.

A single-function Excel formula will adhere to the following basic structure:

= FUNCTION ( Arguments )

Equal sign.

Required so that Excel will treat the contents as a formula.

Function name.

The name of the operation you want to perform.

Opening parenthesis.

Values or references that the function uses to compute a result.

Commas separate multiple arguments.

Closing parenthesis.

Figure 10. Anatomy of an Excel function

Formulas can contain a combination of values, references, simple math and string operators, and functions.

Operation Simple

operator

Example Equivalent function

Equivalent formula using functions

Result

Grouping () =(6/3)*((9-6)/2) =PRODUCT(6/3,(9-6)/2) 3

Exponents (𝒙^𝒚) ^ =2^10 POWER =POWER(2,10) 1024

Multiplication (𝒙 × 𝒚) * =9*9 PRODUCT =PRODUCT(9,9) 81

Division (𝒙 ÷ 𝒚) / =144/32.5 =PRODUCT(144,1/32.5) 4.430769

Addition (𝒙 + 𝒚) + =12+8 SUM =SUM(12,8) 20

Subtraction (𝒙 − 𝒚) - =6-12-3 =SUM(6,-12,-3) -9

Concatenation & ="Learn"&"Key" CONCAT =CONCAT("Learn","Key") LearnKey Figure 11. Examples of simple operators that can be used in Excel formulas

Purpose

Upon completing this exercise, you will be able to recognize the parts of a formula and add a formula to a cell.

Steps for Completion

1. What two elements are missing from the formula ? 2. What is missing from the formula ?

3. What is wrong with the formula ? 4. What is wrong with the formula ? 5. Create a new Excel workbook.

6. Choose a whole number between 1 and 9 and enter that value in cell A1.

7. In cell A2, enter the following formula: =(SUM(A1,9)*2-4)/2-A1+30

8. In cell A3, enter a formula that uses simple operators to multiply cell A1 by 3.

9. In cell A4, enter a formula that uses a function to multiply cell A2 by cell A3.

Project Details

Project file N/A

Estimated completion time 10–15 minutes

Video reference Domain 4

Topic: Insert References; Calculate and Transform Data

Subtopic: Relative, Absolute, and Mixed References

Objectives covered 4 Perform Operations by Using

Formulas and Functions

(3)

61 | Domain 4: Relative, Absolute, and Mixed References Excel 2019 Project Workbook, First Edition 10. What is the value of A4?

11. Change A1 to other whole numbers between 1 and 9. Describe the pattern you see in cell A4.

12. Describe the pattern you see in cell A2.

(4)

62 | Domain 4: Relative, Absolute, and Mixed References Excel 2019 Project Workbook, First Edition

◢ A B C 1 3 5 8 2 6 =A1 3 9

Relative, Absolute, and Mixed References

Excel formulas can pull data from your spreadsheet and other outside sources. In one cell, you can import data from another cell or cell range using a reference, which is the cell range’s address or name. There are four categories of simple cell reference: relative, absolute, mixed, and named.

Figure 12 Figure 13

In Figure 12, B2 will show the contents of A1 regardless of which type of reference to cell A1 is used. Imagine that we create a simple formula in cell B2 that

references cells A1 and A2. No matter which of the four reference types is used, cell B2 will always refer to A1 and A2. However, if cell B2 is copied or filled to C3, Excel will modify the formula that will be placed in C3 as follows:

Reference type

B2 formula C3 formula Description

Relative =SUM(A1,A2) =SUM(B2,B3) A1 is one row above B2 and one column left of B2, so the formula in C3 will be related the same way: B2 is one row above and one column to the left of C3.

Absolute =SUM($A$1,$A$2) =SUM($A$1,$A$2) Both the row and column remain the same.

Mixed =SUM($A1,$A2) =SUM($A2,$A3) The column remains the same, but the row moves relative to the new cell.

=SUM(A$1,A$2) =SUM(B$1,B$2) The row remains the same, but the column changes relative to the new cell.

Named =NamedRange =NamedRange The reference remains the same.

Figure 14. Examples of range references using the data in Figure 12 and Figure 13

Purpose

Upon completing this project, you will understand how to create relative, absolute, and mixed references.

Project Details

Project file

411-student budget.xlsx

Estimated completion time 5 minutes

Subtopic: Relative, Absolute, and Mixed References

Objectives covered 4 Perform Operations by Using Formulas and Functions

4.1 Insert references; Calculate and Transform Data

4.1.1 Insert relative, absolute, and mixed references

(5)

63 | Domain 4: Relative, Absolute, and Mixed References Excel 2019 Project Workbook, First Edition

Steps for Completion

1. What character is used for an absolute reference?

2. What is a mixed reference?

3. Open the 411-student budget workbook. Open the Budget worksheet.

4. Using the SUM function and relative references, total the January amounts in cell B8 and fill the formula to C8:M8.

5. Using the PRODUCT function, one absolute reference, and one relative reference, create a formula in cell B9 that multiplies the tax rate by the total and fill the formula to C9:M9.

6. Save the workbook in the Student folder as an Excel workbook with the name 411-Excel-finished

(6)

64 | Domain 4: Use Named Ranges and Tables in Formulas Excel 2019 Project Workbook, First Edition

Use Named Ranges and Tables in Formulas

When a cell is named, the name can be used instead of the cell address as a reference in a formula; this can make formulas more readable. A reference to a table name can also be used as an argument in many formulas and will import the entire data portion of the table into the formula.

Excel has several features that can help you understand what formulas do and what arguments are required. When you begin typing a formula in a cell, for example, Excel displays tooltips that show the function’s description and arguments. You can also click the Insert Function button below the ribbon, which will display the Insert Function dialog box. You can use the Insert Function dialog box to search for functions. After you select a function and click OK, the Function Arguments dialog box will appear (see Figure 16). This dialog box displays helpful information about the formula arguments. It also allows you to fill in values and references instead of typing the formula by hand. If the cell already contains a formula, the Insert Function button will display the Function Arguments dialog box.

Figure 15

Figure 16

Purpose

Upon completing this project, you will know how to name ranges and use named ranges and table names as references in a formula.

Steps for Completion

1. Open the 412-student budget workbook.

2. Open the Table worksheet.

3. Name cell B11 TaxRate

4. Use the function bar or the Insert Function button to create a formula that will multiply the TaxRate cell by the January total and fill the formula to the remaining 11 months.

5. Using a named reference to a table, create a formula in cell B13 that sums all the values in the Budget table.

Project Details

Project file

412-student budget.xlsx

Estimated completion time 5 minutes

Subtopic: Use Named Ranges and Tables in Formulas

4.1 Insert references

4.1.2 Reference named ranges and named tables in formulas

(7)

65 | Domain 4: Use AVERAGE, SUM, MIN, and MAX Excel 2019 Project Workbook, First Edition

Use AVERAGE, SUM, MIN, and MAX

Many of the calculations that you will perform in Excel will be done using functions. Recall that functions are built-in statements that Excel will calculate. A function can be used in a formula by typing the function name after the equal sign or selecting the function from the Function Library group on the Formulas tab. All basic functions covered in this project can be found under the AutoSum drop-down button located on the Formulas tab or the Editing group of the Home tab.

If your formula doesn’t calculate, check that the following are true:

1. The formula starts with an equal sign (=) 2. The cell does not use the Text number format 3. You have pressed Enter or selected a different cell

4. The cell does not contain any errors. A cell may contain an error if any of the following are true:

a. A triangle shape ◤ appears in the upper left corner of the cell b. The cell shows text that begins with a number sign: #NAME?,

#N/A, #DIV/0!, #VALUE!, #NULL!, #NUM!, #REF!

c. An exclamation point icon appears next to a cell. You can click this icon to troubleshoot the error

Purpose

Upon completing this project, you will know how to apply the AVERAGE, SUM, MIN, and MAX functions to perform calculations with a range of data.

Steps for Completion

1. Open the 421-classes by month workbook. Open the January worksheet.

2. In cell O6, use the SUM function to create a formula that adds the number of attendees for each class (cells B6:M6).

3. Fill the formula down column O until the end of the morning classes.

4. (True or False) It is important to keep functions consistent in a worksheet.

5. In column P, use the AVERAGE function to display the average number of attendees per class section with one decimal place of accuracy.

6. (True or False) When working with averages, a blank cell is the same as a zero in the cell.

7. What do you need to do to a blank cell to make it count against the average?

8. In columns Q and R, use the MIN and MAX functions to display the number of attendees in the smallest and largest section of each class.

Project Details

Project file

421-classes by month.xlsx Estimated completion time 15 minutes

Subtopic: Use AVERAGE, SUM, MIN, and MAX

4.2 Calculate and transform data 4.2.1 Perform calculations by using the AVERAGE(), MAX(), MIN(), and SUM() functions

(8)

66 | Domain 4: Use COUNT, COUNTA, and COUNTBLANK Excel 2019 Project Workbook, First Edition

Use COUNT, COUNTA, and COUNTBLANK

The COUNT function will count the number of cells within a range that contain numbers. The COUNTA function will count the number of cells within a range that contain anything. The COUNTBLANK function will count only empty cells in a range. A cell is empty if it has nothing in it—not even a zero or a quotation mark.

The status bar is found at the bottom of the Excel window, beneath the worksheet and scroll bars. The status bar is a useful tool that shows the status of Excel and has some display options such as a zoom slider and view mode buttons. The status bar can also show you statistical information about any selected range without adding any formula cells to your worksheet. By default, the right side of the status bar shows the average, count, and sum of a selected range. You can right-click the status bar to add or remove options.

Purpose

Upon completing this project, you will know how to apply the COUNT, COUNTA, and COUNTBLANK functions to calculate a range of data. You will also know how to get this data without applying a function to a cell in the worksheet.

Steps for Completion

2. In cell S6, use the COUNT function to create a formula that counts the number of class sections that were taught (cells B6:M6).

3. Fill the formula down column S through the end of the morning classes.

4. In column T, use the COUNTBLANK function to display the number of sections per morning class that were skipped.

5. Use the automatic calculation feature on the status bar to find the average, count, and sum of all the Clearwater evening class sections for January 14–18 (C23:C36):

a. Average:

b. Count:

c. Sum:

Project Details

Project file

Subtopic: Use COUNT, COUNTA, and COUNTBLANK

4.2 Calculate and transform data 4.2.2 Count cells by using the COUNT(), COUNTA(), and COUNTBLANK() functions

(9)

67 | Domain 4: Use the IF Function Excel 2019 Project Workbook, First Edition

Use the IF Function

The functions found under the Logical drop-down button on the Formulas tab all deal with Boolean algebra. Boolean algebra is named after the 19^th-century mathematician and logician George Boole, who pioneered the use of formulas to describe logic. Boolean algebra is one of the foundations of computer science and electronic engineering. You can learn more about George Boole’s life, work, and legacy at https://georgeboole.com/boole.

In standard mathematics, answers are mainly in the form of numbers, but in Boolean statements, there are only two possible answers: true or false. In Excel, true is written TRUE or 1, and false is written FALSE or 0. In logical formulas, any value that is not equal to zero or FALSE will return TRUE.

The IF function accepts three arguments or parts:

1. A logical test or condition, which is evaluated as a Boolean statement 2. A value to return if the condition is true. If this is missing and the

condition is true, the function will return TRUE

3. A value to return if the condition is false. If this is missing and the condition is false, the function will return FALSE

At least one value must be present in the formula, but if one is missing, the IF function will return TRUE or FALSE in its place.

Given the spreadsheet in , shows a variety of IF formulas and their results.

◢ A B C

1 3 FALSE 1 2 6 0 #N/A 3 9 -5

Figure 17

Purpose

Figure 18. IF examples using the data in Figure 16

Upon completing this project, you will be able to create a conditional IF function.

Steps for Completion

2. Using the Formula Bar or the Insert Function button to create an IF function in cell Q2. The IF function should check to see if the total number of class sessions that were held (the sum of cells S6:S20) was greater than or equal to the goal amount of 175. If the goal was achieved, the function should display YES; otherwise, it should display NO.

Project Details

Project file

Subtopic: Use the IF Function Objectives covered

4 Perform Operations by Using Formulas and Functions

4.2 Calculate and transform data 4.2.3 Perform conditional operations by using the IF() function

= IF ( Logical test , Value if true , Value if false ) Result

= IF ( A1+A2=A3 , A3+(A2-A1) , A3+(A3+A1)/2 ) 12

= IF ( A2<5 , "lower" , "higher" ) higher

= IF ( A3>=10 , A3-10 , A3 ) 9

= IF ( B1 , "Real" , "Imaginary" ) Imaginary

= IF ( B2 , "Abracadabra" , ) FALSE

= IF ( B3 , , "Abracadabra" ) TRUE

= IF ( C1<>1 , C1&" widgets" , C1&" widget" ) 1 widget

= IF ( ISERROR(C2) , "Try again" , C2 ) Try again

= IF ( ISBLANK(C3) , FALSE , TRUE ) FALSE