WGU C268 SPREADSHEETS EXAM PREASSESSMENT AND OBJECTIVE ASSESSMENT 2024 | ACCURATE AND VERIFIED EXAM VERSION WITH A STUDY GUIDE AND KNOWLEDGE CHECK FLASHCARDS Use the AND function to complete the "Time Bonus?" column of table 1. An employee earns a time bonus if the project's "Hours Worked" are fewer than the "Estimated Hours" and if the work "Quality" is greater than 1. - CORRECT ANSWER =AND(E1 6<C16,H16>1) Use the OR function to complete the "Outcome Bonus?" column of table 1. An employee earns an outcome bonus if the difficulty of a job is greater than 3 or if the quality of their work is equal to 3. - CORRECT ANSWER =OR(G16>3,H16=3) Use the IF function to complete the "Time Bonus $" column of table 1. If an employee earns a time bonus (i.e., the corresponding cell in the "Time Bonus?" column is TRUE), then "Time Bonus $" is the "Job Pay" for that project times the bonus percentage in cell M1 1. Otherwise "Time Bonus $" is 0. - CORRECT ANSWER =IF(I16,K16*$M$11,0) Use the IF function to complete the "Outcome Bonus $" column of table 1. If an employee earns an outcome bonus (i.e., the corresponding cell in the "Outcome Bonus?" column is TRUE), then "Outcome Bonus $" is the "Job Pay" for that project times the outcome bonus percentage in cell M12; otherwise, "Outcome Bonus $" is 0. - CORRECT ANSWER =IF(J16,K16*$M$12,0) Use the IF function to complete the "Comments" column of table 1. Display "G ood Job" if both the "Hours Worked" are less than or equal to the "Estimated Hours" for a project and the assessed "Quality" of that project is greater than 1. Display "Too Much Time" if the "Hours Worked" on a project exceed the "Estimated Hours" for that project; otherwise, display "Poor Quality." - CORRECT ANSWER =IF(AND(E16<=C16,H16>1),"Good Job",IF(E16>C16,"Too Much time","Poor Quality")) Use the VLOOKUP function to complete the "Employee" column of table 2. Use "Job ID" from table 2 as your lookup_v alue(s) and table 1 as the reference table. - CORRECT ANSWER =VLOOKUP(B40,$B$16:$O$35,3,FALSE) Use the VLOOKUP function to complete the "Difficulty" column of table 2. Again, use "Job ID" from table 2 as the lookup_value(s) and table 1 as the reference t able. - CORRECT ANSWER =VLOOKUP(B40,$B$16:$O$35,6,FALSE) Use the COUNTIF function to complete the "# of Jobs" column in table 3. Reference the appropriate field in table 1 as your range and the "Employee" names in table 3 as your criteria. - CORRECT ANS WER =COUNTIF($D$16:$D$35,G39) Use the SUMIF function to complete the "Total Hours" column in table 3. Reference the appropriate field in table 1 as your range and the "Employee" names in table 3 as your criteria. - CORRECT ANSWER =SUMIF($D$16:$D$35,G39,$ E$16:$E$35) 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. - CORRECT 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. - CORRECT AN SWER =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. - CORRECT ANSWER =SUMIF($D$40:$D$4 6,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. - CORRECT 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. - CORRECT ANSWER =LEN(D9) Use the SEARCH function in cell D11 to determine the position of the "#" character in the statement template (cell D9). - CORRECT ANSWER =SEARCH("#",D9) Use the SEARCH function in cell D12 to determine the position of the "$" character in the statement template (cell D9). - CORRECT ANSWER =SEARCH("$",D9) Use the LEFT function in cell D13 to return th e 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. - CORRECT ANSWER =LEFT(D9,D12) Use the MID function in cell D14 to return the t ext "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 (i n cell D12) as the "num_char" argument . - CORRECT 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 D 10) and the location of the "#" character (in cell D11) as the "num_char" argument. - CORRECT 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 t o complete the "Month" column of the table. - CORRECT 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. - CORREC T 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. - CORRECT 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. - CORRECT 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. - CORRECT 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. - CORRECT ANSWER =CONCAT(E18,"/",F18)
