Amortization
Formulas Used:
PMT(C13/12,C12,C11)
C13 equals the interest rate/12 C12 equals the # of months C11 equals the total loan amount $
Four Js Painting
Formulas Used:
=AND(E16<C16,H16>1)
If the Hours Worked is less than the Estimated Hours, and the Quality is greater than 1, The time bonus is True. E16 equals ‘Hours Worked’. C16 equals ‘Estimated Hours =OR(G16>3,H3=3)
If the Difficulty is greater than 3, or the Quality of work equals 3, the outcome bonus is true. =IF(I16=TRUE,K16*M$11)
If the ‘Time Bonus’ is equals to True, then the ‘Time Bonus $’ is the ‘Job Pay’ * the ‘bonus percentage’ in M11, other wise ‘Time Bonus $’ is equal to zero. I16 is ‘Time Bonus’ = True =IF(J16=TRUE,K16*M$12,0)
If the ‘Outcome Bonus $’ =TRUE M$12 = outcome bonus % or 10% =IF(AND(E16<=C16,H16>1),"Good Job",IF(E16>C16,"Too Much Time","Poor" ))
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" in the cell. E16 equals ‘Hours Worked’ C16 equals ‘Estimated Hours’ =VLOOKUP(B40,B16:D35,3,FALSE)
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 B16:D35 equals reference table 3 equals the 3 column in the reference table range B16:D35 =VLOOKUP(B40,B16:G35,6,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. B16:D35 equals reference table 6 equals the 6th column in the reference table range B16:D35, which equals Difficulty. =COUNTIF(B16:H35,G39)
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. B16:H35 is the range for the reference table
Practice Test