C268 Spreadsheets Comprehensive Questions and Solutions Graded A+

C268 Spreadsheets
Comprehensive Questions and
Solutions Graded A+
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)



Use the RIGHT function in cell D15 to return the text "on:" from the statement template in cell
D9. Use the difference between the length of the statement template (in cell D10) and the
location of the "#" character (in cell D11) as the "num_char" argument. - Answer:
=RIGHT(D9,D10-D11)



Use the MONTH function in cell E18 to calculate the month portion of the "Time Stamp" in cell
B18. Copy and paste your function down to complete the "Month" column of the table. -
Answer: =MONTH(B18)



Use the DAY function in cell F18 to calculate the day portion of the "Time Stamp" in cell B18.
Copy and paste your function down to complete the "Day" column of the table. - Answer:
=DAY(B18)

, Use the HOUR function in cell G18 to calculate the hour portion of the "Time Stamp" in cell B18.
Copy and paste your function down to complete the "Hour" column of the table. - Answer:
=HOUR(B18)



Use the MINUTE function in cell H18 to calculate the minute portion of the "Time Stamp" in cell
B18. Copy and paste your function down to complete the "Minute" column of the table. -
Answer: =MINUTE(B18)



Use the SECOND function in cell I18 to calculate the second portion of the "Time Stamp" in cell
B18. Copy and paste your function down to complete the "Second" column of the table. -
Answer: =SECOND(B18)



Use the CONCAT function (or the CONCATENATE function if you are using Excel 2013 or earlier)
in cell J18 to create the "Date" by combining the "Month" in cell E18 with the "Day" in cell F18.
"Date" should use this syntax: "Month/Day". Your function should, therefore, also insert the "/"
character between the "Month" and "Day." Copy and paste your formula down to complete the
"Date" column of the table. - Answer: =CONCAT(E18,"/",F18)



Use the CONCAT function (or the CONCATENATE function if you are using Excel 2013 or earlier)
to create the "Transaction Statement" in cell K18. The statement in cell K18 should read "I spent
$24.06 at merchant #4931 on: 8/1". To make this statement, combine "Statement P1" in cell
D13, the "Amount" in cell D18, "Statement P2" in cell D14, the "Merchant ID" in cell C18,
"Statement P3" in cell D15, and the "Date" in cell J18. Copy and paste your function down to
complete the "Transaction Statement" column of the table. - Answer:
=CONCAT($D$13,D18,$D$14,C18,$D$15,J18)



In the "Input Analysis" section of the spreadsheet model, calculate the average attendance and
sales for each type of product from the past events listed in the "Past Events" worksheet. -
Answer: =AVERAGE('Past Events'!C4:C103)



In the "Input Analysis" section of the spreadsheet model, calculate the sample standard
deviation for attendance and sales for each type of product from the past events listed on the