WGU C268 STUDY GUIDE FOR EXCEL PA COMPREHENSIVE STUDY GUIDE WITH SOLUTIONS

WGU C268 STUDY GUIDE FOR EXCEL PA
COMPREHENSIVE STUDY GUIDE WITH
SOLUTIONS

◉ 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..
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 M11.
Otherwise "Time Bonus $" is 0..
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..
Answer: =IF(J16,K16*$M$12,0)

,◉ Use the IF function to complete the "Comments" column of table
1. Display "Good 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.".
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_value(s) and table 1
as the reference table..
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 table..
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..
Answer: =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..
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..
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)