WGU C268 SPREADSHEETS FINAL
REVIEW QUESTIONS VERIFIED COMPLETE
ANSWERS 100 PERCENT CORRECT
◉ Use the SUMIF function to complete the "Total Pay" column in
table 3. Reference the "Employee" field in table 1 as your range,
the "Employee" names in table 3 as your criteria, and the "Total
Pay" field in table 1 as your sum_range.
Answer: =SUMIF($D$16:$D$35,G39,$N$16:$N$35)
◉ Use the COUNTIF function to complete the "# of Touch-ups"
column in table 4. Reference the appropriate field in table 2 as
your range and the "Difficulty" rating in table 4 as your criteria.
Answer: =COUNTIF($D$40:$D$46,G46)
◉ Use the SUMIF function to complete the "Cost Touch-ups"
column in table 4. Reference the appropriate field in table 2 as
your range and the "Difficulty" rating in table 4 as your criteria.
Answer: =SUMIF($D$40:$D$46,G46,$E$40:$E$46)
◉ Use the AVERAGEIF function to complete the "Average
Hours/Job" column in table 4. Reference the appropriate field in
table 1 as your range and the "Difficulty" rating in table 4 as your
criteria.
Answer: =AVERAGEIF($G$16:$G$35,G46,$E$16:$E$35)
, ◉ Use the LEN function in cell D10 to determine the number of
characters in the statement template in cell D9.
Answer: =LEN(D9)
◉ Use the SEARCH function in cell D11 to determine the position
of the "#" character in the statement template (cell D9).
Answer: =SEARCH("#",D9)
◉ Use the SEARCH function in cell D12 to determine the position
of the "$" character in the statement template (cell D9).
Answer: =SEARCH("$",D9)
◉ Use the LEFT function in cell D13 to return the text "I spent $"
from the statement template in cell D9. Refer to the location of the
"$" character you calculated in cell D12 as the "num_char"
argument for your function.
Answer: =LEFT(D9,D12)
◉ Use the MID function in cell D14 to return the text "at merchant
#" from the statement template in cell D9. Refer to the location of
the "$" (in cell D12)—adjusted by adding 1—as the "start_num"
argument. Use the difference between the location of the "#"
character (in cell D11) and the "$" character (in cell D12) as the
"num_char" argument.
Answer: =MID(D9,D12+1,D11-D12)
REVIEW QUESTIONS VERIFIED COMPLETE
ANSWERS 100 PERCENT CORRECT
◉ Use the SUMIF function to complete the "Total Pay" column in
table 3. Reference the "Employee" field in table 1 as your range,
the "Employee" names in table 3 as your criteria, and the "Total
Pay" field in table 1 as your sum_range.
Answer: =SUMIF($D$16:$D$35,G39,$N$16:$N$35)
◉ Use the COUNTIF function to complete the "# of Touch-ups"
column in table 4. Reference the appropriate field in table 2 as
your range and the "Difficulty" rating in table 4 as your criteria.
Answer: =COUNTIF($D$40:$D$46,G46)
◉ Use the SUMIF function to complete the "Cost Touch-ups"
column in table 4. Reference the appropriate field in table 2 as
your range and the "Difficulty" rating in table 4 as your criteria.
Answer: =SUMIF($D$40:$D$46,G46,$E$40:$E$46)
◉ Use the AVERAGEIF function to complete the "Average
Hours/Job" column in table 4. Reference the appropriate field in
table 1 as your range and the "Difficulty" rating in table 4 as your
criteria.
Answer: =AVERAGEIF($G$16:$G$35,G46,$E$16:$E$35)
, ◉ Use the LEN function in cell D10 to determine the number of
characters in the statement template in cell D9.
Answer: =LEN(D9)
◉ Use the SEARCH function in cell D11 to determine the position
of the "#" character in the statement template (cell D9).
Answer: =SEARCH("#",D9)
◉ Use the SEARCH function in cell D12 to determine the position
of the "$" character in the statement template (cell D9).
Answer: =SEARCH("$",D9)
◉ Use the LEFT function in cell D13 to return the text "I spent $"
from the statement template in cell D9. Refer to the location of the
"$" character you calculated in cell D12 as the "num_char"
argument for your function.
Answer: =LEFT(D9,D12)
◉ Use the MID function in cell D14 to return the text "at merchant
#" from the statement template in cell D9. Refer to the location of
the "$" (in cell D12)—adjusted by adding 1—as the "start_num"
argument. Use the difference between the location of the "#"
character (in cell D11) and the "$" character (in cell D12) as the
"num_char" argument.
Answer: =MID(D9,D12+1,D11-D12)
