CGS 2518 EXAM QUESTIONS WITH
VERIFIED ANSWERS
The original mortgage loan was for $300,000 with a 5% APR for 30 years. You want to
calculate the interest on the last monthly payment at the end of the 15th year. What
value should be referenced for the per argument in the IPMT function? Hint: Number of
payment periods in a year is 12. - Answer-A. 180
A local police office wants to create a rule that if an officer pulls over a person for
exceeding the speed limit by at least five miles per hour or if that person has two or
more speeding violations on record, the officer will fine the speeder the higher of $200
or $50 for each mile over the speed limit. Otherwise, the fine is $45. The speed limit is
entered in cell B5, the person's speed is entered in cell B10, and the person's number of
previous tickets is entered in cell B11. What function derives the correct answer?
a. =IF(OR(B10-B5>=5, B11>=2), MAX(200, (B10-B5)*50), 45)
b. =IF(OR(B10>B5, B11>=2), MAX(200, 50), 45)
c. =IF(AND(B10-B5>=5, B11<2), MAX(200, (B10-B5)*50), 45)
d. =IF(AND(B10>B5, B11>=2), 200, 45) - Answer-a. =IF(OR(B10-B5>=5, B11>=2),
MAX(200, (B10B5)*50), 45)
What function would you use to calculate the total number of periods in a loan or
investment? - Answer-C. NPER
Which type of chart is the least appropriate for depicting yearly rainfall totals for five
cities for four years? - Answer-Pie chart
Look at the stacked bar chart in Figure.3.35. Which of the following is a category on the
category axis? - Answer-CIS Managers
Which of the following is NOT a type of sparkline? - Answer-bar
If you want to show exact values for a data series in a bar chart, which chart element
should you display? - Answer-data labels
The value axis currently shows increments such as 50,000 and 100,000. What option
would you select to display the values in increments of 50 and 100? - Answer-show axis
in thousands
You want to create a single chart that shows the proportion of yearly sales for five
divisions for each year for 5 years. Which type of chart can accommodate your needs? -
Answer-100% stacked column chart
, Currently, a column chart shows values on the values axis, years on the category axis,
and state names in the legend. What should you do if you want to organize data with
the states on the category axis and years shown on the legend? - Answer-click switch
row/column in the data group on the Design tab
What do you click to remove a data series from a chart so that you can focus on other
data series? - Answer-chart filters
Which of the following does not display automatically when you create a clustered
column chart? - Answer-data labels
After you create a line type sparkline, what option should you select to display dots for
each data point? - Answer-markers
A workbook contains sales information for the first quarter and you are interested in
finding the total sales generated in January by the sales rep Jim Anderson. What
function is best suited to handle the task? - Answer-SUMIFS
What function would you use to identify a value's rank as a percent, excluding 0 and 1?
- Answer-PERCENTRANK.EXC
What does a negative COVARIANCE indicate? - Answer-An inverse relationship
between the datasets
A worksheet contains sales dollars for agents with your company. The values are
$1,250, $1090, $985, $985, $880, $756, $675, $650, and $600. Using the RANK AVG
function that is considered a best practice, what is the rank of the third values which are
both 985? - Answer-none of the above
What function would you use to calculate the strength of a relationship between two or
more variables? - Answer-correl
Your hypothesize that there is a relationship between lack of regular exercise and
illness. To research this theory, you have compiled a sample set of data that contains
numbers of days in which an hour or more of exercise is completed as well as numbers
of days sick within a calendar year. What tools in Excel could you use to investigate the
relationship between the data? - Answer-correl
Which of the following functions has the ability to calculate the (MU) of a dataset based
on a user defined set of criteria? - Answer-AVERAGEIFS
What is the difference between STDEV.S and STDEV.P? - Answer-STDEV.S calculates
standard deviation of a sample; STDEV.P calculates standard deviation of a population
What is the difference between a sample and a population? - Answer-A population
contains all data you want to evaluate, while a sample contains a portion of the data.
VERIFIED ANSWERS
The original mortgage loan was for $300,000 with a 5% APR for 30 years. You want to
calculate the interest on the last monthly payment at the end of the 15th year. What
value should be referenced for the per argument in the IPMT function? Hint: Number of
payment periods in a year is 12. - Answer-A. 180
A local police office wants to create a rule that if an officer pulls over a person for
exceeding the speed limit by at least five miles per hour or if that person has two or
more speeding violations on record, the officer will fine the speeder the higher of $200
or $50 for each mile over the speed limit. Otherwise, the fine is $45. The speed limit is
entered in cell B5, the person's speed is entered in cell B10, and the person's number of
previous tickets is entered in cell B11. What function derives the correct answer?
a. =IF(OR(B10-B5>=5, B11>=2), MAX(200, (B10-B5)*50), 45)
b. =IF(OR(B10>B5, B11>=2), MAX(200, 50), 45)
c. =IF(AND(B10-B5>=5, B11<2), MAX(200, (B10-B5)*50), 45)
d. =IF(AND(B10>B5, B11>=2), 200, 45) - Answer-a. =IF(OR(B10-B5>=5, B11>=2),
MAX(200, (B10B5)*50), 45)
What function would you use to calculate the total number of periods in a loan or
investment? - Answer-C. NPER
Which type of chart is the least appropriate for depicting yearly rainfall totals for five
cities for four years? - Answer-Pie chart
Look at the stacked bar chart in Figure.3.35. Which of the following is a category on the
category axis? - Answer-CIS Managers
Which of the following is NOT a type of sparkline? - Answer-bar
If you want to show exact values for a data series in a bar chart, which chart element
should you display? - Answer-data labels
The value axis currently shows increments such as 50,000 and 100,000. What option
would you select to display the values in increments of 50 and 100? - Answer-show axis
in thousands
You want to create a single chart that shows the proportion of yearly sales for five
divisions for each year for 5 years. Which type of chart can accommodate your needs? -
Answer-100% stacked column chart
, Currently, a column chart shows values on the values axis, years on the category axis,
and state names in the legend. What should you do if you want to organize data with
the states on the category axis and years shown on the legend? - Answer-click switch
row/column in the data group on the Design tab
What do you click to remove a data series from a chart so that you can focus on other
data series? - Answer-chart filters
Which of the following does not display automatically when you create a clustered
column chart? - Answer-data labels
After you create a line type sparkline, what option should you select to display dots for
each data point? - Answer-markers
A workbook contains sales information for the first quarter and you are interested in
finding the total sales generated in January by the sales rep Jim Anderson. What
function is best suited to handle the task? - Answer-SUMIFS
What function would you use to identify a value's rank as a percent, excluding 0 and 1?
- Answer-PERCENTRANK.EXC
What does a negative COVARIANCE indicate? - Answer-An inverse relationship
between the datasets
A worksheet contains sales dollars for agents with your company. The values are
$1,250, $1090, $985, $985, $880, $756, $675, $650, and $600. Using the RANK AVG
function that is considered a best practice, what is the rank of the third values which are
both 985? - Answer-none of the above
What function would you use to calculate the strength of a relationship between two or
more variables? - Answer-correl
Your hypothesize that there is a relationship between lack of regular exercise and
illness. To research this theory, you have compiled a sample set of data that contains
numbers of days in which an hour or more of exercise is completed as well as numbers
of days sick within a calendar year. What tools in Excel could you use to investigate the
relationship between the data? - Answer-correl
Which of the following functions has the ability to calculate the (MU) of a dataset based
on a user defined set of criteria? - Answer-AVERAGEIFS
What is the difference between STDEV.S and STDEV.P? - Answer-STDEV.S calculates
standard deviation of a sample; STDEV.P calculates standard deviation of a population
What is the difference between a sample and a population? - Answer-A population
contains all data you want to evaluate, while a sample contains a portion of the data.
