Excel - Chapter 2 - Working with Formulas and Functions 10/25/23, 9:47 PM

Excel - Chapter 2 - Working with Formulas and

PAUSE & PRACTICE: EXCEL 2-3

For this project, you continue working on the workbook for Perfect Vacation Rentals. You use Text functions to display data
as well as SUMIF and SUMPRODUCT to complete totals.

File Needed: [your initials] PP [Link]

1. Open the [your initials] PP [Link] workbook completed in Pause & Practice 2-2 and save it as [your initials] PP
E2–3.
2. Click the Rental Days worksheet tab.
3. Enter a TEXTJOIN function to display property and state names in the cell.
a. Click cell G5.
b. Click the Text button [Formulas tab, Function Library group] and select TEXTJOIN. The Function Arguments
dialog box opens.
c. Click the Delimiter entry box.
d. Type , (a comma) and press the Spacebar to set a comma followed by a space as the delimiter characters.
e. Click the Text1 box and select cell A5. The Ignore_empty argument is blank or true, so that blank cells are
ignored.
f. Click the Text2 box and select cell C5. The result for the cell G5 displays in the dialog box. Verify that it is
“Chalet Soleil, IN” (Figure 2-65).

Figure 2-65 Build a TEXTJOIN function

g. Click OK.
h. Click and drag the Fill pointer to copy the formula in cell G5 to cells G6:G19. Double-clicking the Fill pointer
does not work because Excel needs data in the adjacent column to determine how far to fill.
i. AutoFit column G.
4. Use CONCAT to display city names and totals in the cell.
a. Click cell I5.

[Link] Page 1 of 4
Excel - Chapter 2 - Working with Formulas and Functions 10/25/23, 9:47 PM

b. Click the Text button [Formulas tab, Function Library group] and select CONCAT.
c. Click the Text1 entry box and select cell B5.
d. Click the Text2 entry box.
e. Type , (a comma) and press the Spacebar to set a comma followed by a space as the Text2 argument.
f. Click the Text3 box and select cell D5. The result for cell I5 displays in the dialog box. Verify that it is “Bass Lake,
20” (Figure 2-66).

Figure 2-66 Build a CONCAT function

g. Click OK.
h. Click and drag the Fill pointer to copy the formula in cell I5 to cells I6:I19.
i. AutoFit column I.
j. Press Ctrl+Home.
5. Use SUMIF to calculate results.
a. Click the Guest Data worksheet tab.
b. Select cell H4.
c. Click the Math & Trig button [Formulas tab, Function Library group] and select SUMIF.
d. Click the Range text box and select cells B4:B38.
e. Press F4 (FN+F4) to make the reference absolute.
f. Press Tab and type g in the Criteria box. Criteria are not case-sensitive. The cells in column B will be checked to
determine if they show “G.”
g. Click the Sum_range box, select cells C4:C38, and press F4 (FN+F4). The values in this range will be summed
for those rows in which the entry is G (Figure 2-67).
h. Click OK. Rental days by groups
are 76.
i. Click cell H4 and use the Fill
pointer to copy the formula to cell
H5.
j. Edit the formula in cell H5 to show
s instead of “g.”
k. Press Ctrl+Home.
6. Use SUMPRODUCT to calculate start-
up costs.
Figure 2-67 Build a SUMIF function
a. Click the Start-Up sheet tab.
b. Select cell C16.
c. Click the Math & Trig button [Formulas tab, Function Library group] and select SUMPRODUCT.
d. Click the Array1 box and select cells C4:C14. The range includes 11 values.

[Link] Page 2 of 4
Excel - Chapter 2 - Working with Formulas and Functions 10/25/23, 9:47 PM

e. Click the Array2 box and select cells D4:D14. The range includes 11 values, and each one will be multiplied by
the corresponding value in the same row in column C.
f. Click OK. The value at cost is 364.43.
g. Click cell C17, type =sump, and press Tab. The function is inserted and the ScreenTip shows the next argument,
array1.
h. Select cells C4:C14 and type , (a comma) to separate the first argument from the second.
i. Select cells E4:E14 (Figure 2-68). You need not type the closing parenthesis.
j. Press Enter. The value is 694.86.
k. Select cells C16:C17 and apply the
Accounting Number Format.
7. Use the ROUND function.
a. Select cell C19.
b. Click the Math & Trig button
[Formulas tab, Function Library
group] and choose ROUND.
c. Click the Number text box if
necessary and click cell C16.
d. Type 0 in the Num_digits text box.
e. Press Enter. The value is rounded
down to $364.00.
f. Copy the formula in cell C19 to cell
C20. The value from cell C17 is
rounded up to $695.00.
g. Press Ctrl+Home.
8. Save and close the workbook (Figure
2-69).
Figure 2-68 Type a SUMPRODUCT function

Figure 2-69 PP E2-3 completed

[Link] Page 3 of 4
Excel - Chapter 2 - Working with Formulas and Functions 10/25/23, 9:47 PM

Page(s)
ID ex21_ip_02_pp3

[Link] Page 4 of 4