Domain 4 Lesson 1: Insert References, Calculate & Transform Data

1 Basic Formulas
​Calculate the total for cells B4, B5, and B6 in cell B8. AutoFill that total through cell M8. Then, in cell B9, calculate the sales tax through the rate in cell Q2. AutoFill those values through to cell M9.

1. Click in cell B8
2. Type: =B4+B5+B6
3. Click the fill handle in cell B8 and drag it through cell M8
4. Click in cell B9
5. Type: =B8*$Q$2
6. Click the fill handle in cell B9 and drag it through cell M9

2 Named Ranges In Formulas
​In cell B9, multiply the contents of cell B8 and the named range, TaxRate. Then, AutoFill the formula to cell M9.

1. Click in cell B9
2. Type: =B8 * TaxRate
3. Click the fill handle in cell B9 and drag it through cell M9

3 Basic Functions
Below each of the headings for Sum, Average, Min, and Max, add functions to get the sum, average, maximum, and minimum values for cells B6 through M6. Then, AutoFill the results down through all of the morning classes.

1. Click in cell O6
2. Type: =SUM(B6:M6)
3. Click in cell P6
4. Type: =AVERAGE(B6:M6)
5. Click in cell Q6
6. Type: =MIN(B6:M6)
7. Click in cell R6
8. Type: =MAX(B6:M6)
9. Select cells O6 through R6
10. Click the fill handle and drag down through row 20

4 Count Functions
​In cell S6, use a function to display a count of the cells from B6 through M6 that contain a number. In cell T6, use a function to display a count of the cells from B6through M6 that contain nothing. AutoFill both cells down through all of the morning classes.

1. Click in cell S6
2. Type: =COUNT(B6:M6)
3. Click in cell T6
4. Type: =COUNTBLANK(B6:M6)
5. Select cells S6 and T6
6. Click the fill handle and drag down through row 20

5 If Functions
In cell Q2, insert a function that displays a YES if the total number of morning classes for January is at least 175 and a NO if not.

1. Click in cell Q2
2. Click the Formulas tab
3. In the Function Library group, click the Logical drop-down arrow
4. Click IF
5. In the Logical_test field type: SUM(S6:S20)>=175
6. Click in the Value_if_true field
7. Type: YES
8. Click in the Value_if_false field
9. Type: NO
10. Click the OK button

Domain 4 Lesson 2: Format & Modify Text

​6 Text Functions
In cell D3, add a function to get the two leftmost numbers from cell B3. In cell E3, add a function to get the middle three numbers from cell B3. In cell F3, add a function to get the two rightmost numbers from cell B3. AutoFill the three results down through the remaining products.

1. Click in cell D3
2. Type: =LEFT(B3,2)
3. Click in cell E3
4. Type: =MID(B3,4,3)
5. Click in cell F3
6. Type: =RIGHT(B3,2)
7. Select cells D3 through F3
8. Click the fill handle and drag through row 8

7 Capitalization
Use the appropriate formulas and Autofill to display the names from cells A2 through B4 in cells D2 through E4, using all capital letters. Display the names from cells A2 through B4 in cells G2 through H4, using all lowercase letters. Use cells J2 through J4 to display the length of the text in cells A2 through A4.​

1. Click in cell D2
2. Type: =UPPER(A2)
3. Click the fill handle and drag to cell E2
4. Click the fill handle and drag to cell E4
5. Click in cell G2
6. Type: =LOWER(A2)
7. Click the fill handle and drag to cell H2
8. Click the fill handle and drag to cell H4
9. Click in cell J2
10. Type: =LEN(A2)
11. Click the fill handle and drag to cell J4

​8 Combining Text
​Display the combined first and last name from cells A2 and B2 in cell L2, adding a space in between the first and last name. Fill those values for the other two names. In cell N2, combine the values of cells H2 and G2 with a delimiter of a comma and ignoring empty cells. Fill those values for the other two names.

1. Click in cell L2
2. In the Formulas tab, Function Library group, click the Text drop-down arrow
3. Click CONCAT
4. Click cell A2 to populate the Text1 field
5. Click in the Text2 field
6. Type: " "
7. Click in the Text3 field
8. Click cell B2
9. Click the OK button
10. Ensure that cell L2 is still selected and click the fill handle and drag to cell L4
11. Click in cell N2
12. In the Formulas tab, Function Library group, click the Text drop-down arrow
13. Click TEXTJOIN
14. In the Delimiter field type:","
15. Click in the Ignore_empty field
16. Type: TRUE
17. Click in the Text1 field
18. Type: H2
19. Click in the Text2 field
20. Type: G2
21. Click the OK button
22. Ensure that cell N2 is still selected and click the fill handle and drag to cell N4