Subdomain 4.1 Insert references

4.1.1 Insert relative, absolute, and mixed references

DOWNLOAD FILE

Directions: (Relative Reference)
1.) Download the file (above) and go to the Relative Reference tab.
2.) Put your cursor in J5.
3.) Click the "Insert Function" button.
4.) Set Number1 by dragging the cursor across E5:I5.
5.) Click OK
6.) Drag cursor down from J5 to J9.

To Get Credit: Show me a completed J column (total hours).

​Directions: (Absolute Reference)
1.) Go to the Absolute Reference tab.
2.) Put your cursor in A11 and hit the equal sign.
3.) Click A5
4.) Click A11 and go to the formula bar and hit F4.

To Get Credit: Show me the formula bar with the $ Absolute reference.

​Directions: (Mixed Reference)
1.) Go to the Mixed Reference tab.
2.) Put your cursor in K5.
3.) Type in =J5/J10
4.) In the formula bar highlight J10 and hit F4.
5.) Hit Enter.

To Get Credit: Show me the formula bar with the Mixed reference.​

---

4.1.2 Reference named ranges and named tables in formulas

DOWNLOAD FILE

Directions:
1.) Download the file (above).
2.) Put your cursor in E10.
3.) Type in =Sum(Monday)/5
4.) Hit enter

To Get Credit: Show me the average hours worked for your five employees on Monday.​​

---

Subdomain 4.2 Calculate and transform data

4.2.1 Perform calculations by using the AVERAGE(), MAX(), MIN(), and SUM() functions

DOWNLOAD FILE

Directions: (AVERAGE)
1.) Download the file (above).
2.) Put your cursor in B12.
3.) Open up formula builder.
4.) Type in AVERAGE
5.) Select the entire range of hours (shown in video)
6.) Click OK
To Get Credit: Show me the formula in box B12.

Directions: (MAX)
1.) Put your cursor in B13.
2.) Open up formula builder.
3.) Type in MAX
4.) Select the entire range of hours (shown in video)
5.) Click OK
To Get Credit: Show me the formula in box B13.

Directions: (MIN)
1.) Put your cursor in B14.
2.) Open up formula builder.
3.) Type in MIN
4.) Select the entire range of hours (shown in video)
5.) Click OK
To Get Credit: Show me the formula in box B14.

Directions: (SUM)
1.) Put your cursor in B15.
2.) Open up formula builder.
3.) Type in SUM
4.) Select the entire range of hours (shown in video)
5.) Click OK
To Get Credit: Show me the formula in box B15.

---

​4.2.2 Count cells by using the COUNT(), COUNTA(), and COUNTBLANK() functions

DOWNLOAD FILE

Directions: (COUNT)
1.) Download the file (above).
2.) Put your cursor in E12.
3.) Open up formula builder.
4.) Type in COUNT
5.) Select the Monday range.
6.) Click OK
To Get Credit: Show me the formula in box E12.

Directions: (COUNTA)
1.) Put your cursor in E13.
2.) Open up formula builder.
3.) Type in COUNTA
4.) Select the numbers under Tuesday (as shown in the video) for Value1.
5.) Click OK
To Get Credit: Show me the formula in box E13.

​Directions: (COUNTBLANK)
1.) Put your cursor in E14
2.) Open up formula builder.
3.) Type in COUNTBLANK
4.) Select the numbers under from Thursday to Friday (as shown in video).
5.) Click OK
To Get Credit: Show me the formula in box E14.

---

4.2.3 Perform conditional operations by using the IF() function

DOWNLOAD FILE

Directions:
1.) Download the file (above).
2.) Click on cell K5
3.) Press Insert Function
4.) Type In IF
5.) For Logical Test click J5 and type in >30 (as shown in video)
6.) For value if true (leave empty) for value if false, type in "Increase Hours"
7.) Drag it down the column (as shown in video).

To Get Credit: Show me column K with the IF function.​

---

Subdomain 4.3 Format and modify text

4.3.1 Format text by using RIGHT(), LEFT(), and MID() functions

DOWNLOAD FILE

Directions:
1.) Download the file (above) and use it for the next 3 tasks
2.) Follow along with the video.


To Get Credit: Follow the directions and fill the spreadsheet.​

---

4.3.2 Format text by using UPPER(), LOWER(), and LEN() functions

Directions: See directions from 4.3.1​

---

4.3.3 Format text by using the CONCAT() and TEXTJOIN() functions

Directions: See directions from 4.3.1​​​