CGS 2518 EXAM QUESTIONS AND
ANSWERS
Using the worksheet shown in Question 11, write a formula to automatically determine
the number of cars that cost more than $20,000. - Answer-
=COUNTIF(B2:B10,">20000")
Using the worksheet showing in Question 11, write a formula to automatically determine
the total value of all GM cars. This formula should work even if the data is later
modified. - Answer-=COUNTIF(A2:A10,"GM",B2:B10)
Explain what a "what-if" analysis is by giving an example based on the worksheet in
Question 11. - Answer-"What-if" analysis is a scenario based analysis where we can
alter few values in memory and identify their effect on a given cell value.
To see the change in total if Lexus is priced at $32,075 and $45,000 we can create
multiple scenarios each having different values to see the effect on total.
Go to Data Tab > What if analysis > Scenario Manager, below pop would appear press
"add", name the scenario here as "Lexus--Original", in the next pop up after clicking OK
enter the value for this scenario, click OK to confirm then scenario manager would
display, add one more scenario as "Lexus--Marked Up", enter scenario value as
$45,000, click OK to confirm, now one can switch between these values by clicking on
"SHOW" button and below two pics depicts these values with two Lexus prices.
Explain what a Goal Seeker is by giving an example based on the worksheet in
Question 11. - Answer-Goal Seeker is an entirely different analysis compared to "What-
if" analysis. It is mainly based to know what value is required if attain a required target.
Example: current total is $188,556, what is the cost of Lexus car to be if this total cost
should become $175,000?
Go to Data Tab > What-IF Analysis > Goal Seek, pop-up appears select the total value
address (B11) in Set Cell (target cell) and required value in "To Value" text box (target
value) and fill in the address of Lexus price ($B$4) in "By changing cell:" and click OK,
Goal Seeker would confirm if there is a definite solution to the given criteria and update
the sheet.
Using the worksheet shown in Question 11, write a formula to determine the value of
the third least expensive car. - Answer-=SMALL(B2:B10,3)
If each car shown in Question 11 is marked up between $100 and $200 in dollar
increments, what function could be used to randomly assign the amount to be added to
the car price in this formula?: =B2+ ______ - Answer-=B2+RANDBETWEEN(100,200)
ANSWERS
Using the worksheet shown in Question 11, write a formula to automatically determine
the number of cars that cost more than $20,000. - Answer-
=COUNTIF(B2:B10,">20000")
Using the worksheet showing in Question 11, write a formula to automatically determine
the total value of all GM cars. This formula should work even if the data is later
modified. - Answer-=COUNTIF(A2:A10,"GM",B2:B10)
Explain what a "what-if" analysis is by giving an example based on the worksheet in
Question 11. - Answer-"What-if" analysis is a scenario based analysis where we can
alter few values in memory and identify their effect on a given cell value.
To see the change in total if Lexus is priced at $32,075 and $45,000 we can create
multiple scenarios each having different values to see the effect on total.
Go to Data Tab > What if analysis > Scenario Manager, below pop would appear press
"add", name the scenario here as "Lexus--Original", in the next pop up after clicking OK
enter the value for this scenario, click OK to confirm then scenario manager would
display, add one more scenario as "Lexus--Marked Up", enter scenario value as
$45,000, click OK to confirm, now one can switch between these values by clicking on
"SHOW" button and below two pics depicts these values with two Lexus prices.
Explain what a Goal Seeker is by giving an example based on the worksheet in
Question 11. - Answer-Goal Seeker is an entirely different analysis compared to "What-
if" analysis. It is mainly based to know what value is required if attain a required target.
Example: current total is $188,556, what is the cost of Lexus car to be if this total cost
should become $175,000?
Go to Data Tab > What-IF Analysis > Goal Seek, pop-up appears select the total value
address (B11) in Set Cell (target cell) and required value in "To Value" text box (target
value) and fill in the address of Lexus price ($B$4) in "By changing cell:" and click OK,
Goal Seeker would confirm if there is a definite solution to the given criteria and update
the sheet.
Using the worksheet shown in Question 11, write a formula to determine the value of
the third least expensive car. - Answer-=SMALL(B2:B10,3)
If each car shown in Question 11 is marked up between $100 and $200 in dollar
increments, what function could be used to randomly assign the amount to be added to
the car price in this formula?: =B2+ ______ - Answer-=B2+RANDBETWEEN(100,200)
