Spreadsheets C268 Exam/14 Questions
and Answers
What-If analysis special cases - -Excel Solver, Scenario Manager, Goal Seek,
Data Table
- What is the correct format for a payment? B11 has loan amount, B12 has
number of payments, and B13 has interest rate - -=PMT(B13/12,B12,B11)
- How can we use payment, loan interest rate, principal, and/or balance to
calculate monthly interest? - -=payment*loan interest/12
- SUMIF, AVERAGEIF, or COUNTIF are conditional functions. So, in the case of
AVERAGEIF, we tell it where to look, what to look for, and then what to
average if we find it there. What would be the most likely variables for the
circled column given that the we are looking at the difficulty column (A) as
the range and the hours worked column (D) to average per job? - -
=AVERAGEIF($A$16:$A$35,A46, $D$16:$D$35)
- If the range is in column D, the criteria is in column G, and the numbers
that need to be summed are in column E, which equation has the correct
format? - -=SUMIF($D$20:$D$35, G16,$E$20:$E$35)
- The IF function is a logic test to check if a condition is occurring and to
produce a certain result if it is occurring. Nested IF is just an IF with another
function(s( added. The format for all IF statements, no matter how
complicated is -IF(logical_test,value_if_true,value_if_false). There are just 3
variables. So if there were 2 conditions for the logic, only one value if BOTH
conditions are met, and only one outcome otherwise, the equation may look
like: - -=IF(AND(E16<=C16,H16>1),"Good Job","Poor Quality")
- Nested IF is just an IF with another function(s) added. The function helps
out by allowing for more choices. The Format for all IF statements, no matter
how complicated, is: =IF(logical_test,value_if_true,value_if_flase). So, if there
were two conditions for the logic, only one value if BOTH conditions are met,
and two outcomes otherwise, the equation may look like: - -
=IF(AND(B166<=C16,G16>1),"NICE",IF(B16>C16,"SLOW","BAD"))
- VLOOKUP and HLOOKUP are used when we have lots of data and don't
want to waste time looking everywhere for a tabular value. If we had a table
with employees names Sam, Sami, Semi, Sem, and Sarah, along with wage
information in row 2, would we put TRUE or FALSE at the end of our
HLOOKUP as we search for Sam's hourly pay? - -
=HLOOKUP(E16:$F$11:$H$12,2,FALSE)
and Answers
What-If analysis special cases - -Excel Solver, Scenario Manager, Goal Seek,
Data Table
- What is the correct format for a payment? B11 has loan amount, B12 has
number of payments, and B13 has interest rate - -=PMT(B13/12,B12,B11)
- How can we use payment, loan interest rate, principal, and/or balance to
calculate monthly interest? - -=payment*loan interest/12
- SUMIF, AVERAGEIF, or COUNTIF are conditional functions. So, in the case of
AVERAGEIF, we tell it where to look, what to look for, and then what to
average if we find it there. What would be the most likely variables for the
circled column given that the we are looking at the difficulty column (A) as
the range and the hours worked column (D) to average per job? - -
=AVERAGEIF($A$16:$A$35,A46, $D$16:$D$35)
- If the range is in column D, the criteria is in column G, and the numbers
that need to be summed are in column E, which equation has the correct
format? - -=SUMIF($D$20:$D$35, G16,$E$20:$E$35)
- The IF function is a logic test to check if a condition is occurring and to
produce a certain result if it is occurring. Nested IF is just an IF with another
function(s( added. The format for all IF statements, no matter how
complicated is -IF(logical_test,value_if_true,value_if_false). There are just 3
variables. So if there were 2 conditions for the logic, only one value if BOTH
conditions are met, and only one outcome otherwise, the equation may look
like: - -=IF(AND(E16<=C16,H16>1),"Good Job","Poor Quality")
- Nested IF is just an IF with another function(s) added. The function helps
out by allowing for more choices. The Format for all IF statements, no matter
how complicated, is: =IF(logical_test,value_if_true,value_if_flase). So, if there
were two conditions for the logic, only one value if BOTH conditions are met,
and two outcomes otherwise, the equation may look like: - -
=IF(AND(B166<=C16,G16>1),"NICE",IF(B16>C16,"SLOW","BAD"))
- VLOOKUP and HLOOKUP are used when we have lots of data and don't
want to waste time looking everywhere for a tabular value. If we had a table
with employees names Sam, Sami, Semi, Sem, and Sarah, along with wage
information in row 2, would we put TRUE or FALSE at the end of our
HLOOKUP as we search for Sam's hourly pay? - -
=HLOOKUP(E16:$F$11:$H$12,2,FALSE)
