CGS Exam 1 review

What should you do if you see pound signs (###) instead of values or results of formulas? 1. (a)Increase the column width. 2. (b)Increase the zoom percentage. 3. (c)Delete the column. 4. (d)Adjust the row height.

(a)Increase the column width.

Which type of chart is the least appropriate for depicting yearly rainfall totals for five cities for four years? 1. (a)Pie chart 2. (b)Line chart 3. (c)Column chart 4. (d)Bar chart

(a)Pie chart

A formula containing the entry =J$7 is copied to a cell three columns to the right and four rows down. How will the entry display in its new location? 1. (a)=M$7 2. (b)=J$7 3. (c)=M11 4. (d)=J$11

(a)=M$7

What function would most efficiently accomplish the same thing as =(B5+C5+D5+E5+F5)? 1. (a)=SUM(B5:F5) 2. (b)=AVERAGE(B5:F5) 3. (c)=MEDIAN(B5:F5) 4. (d)=COUNT(B5:F5)

(a)=SUM(B5:F5)

A workbook contains a list of houses and the months that they were sold in Florida. You are interested in determining the average price of sold houses in June in Ft. Lauderdale. What function is best suited for this calculation? 1. (a)AVERAGEIFS 2. (b)AVERAGEIF 3. (c)SUMIF 4. (d)MAXIFS

(a)AVERAGEIFS

Currently, a column chart shows values on the value 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 the years shown in the legend? 1. (a)Click Switch Row/Column in the Data group on the Design tab. 2. (b)Change the chart type to a clustered column chart. 3. (c)Click Layout 2 in the Chart Layouts group on the Design tab and apply a different chart style. 4. (d)Click Legend in the Labels group on the Layout tab and select Show Legend at Bottom.

(a)Click Switch Row/Column in the Data group on the Design tab.

A worksheet contains sales dollars for agents with your company. The values are $1,250, $1,090, $985, $985, $880, $756, $675, $650, and $600. What function could be used to return the third largest value? 1. (a)FREQUENCY 2. (b)STEYX 3. (c)LARGE 4. (d)SLOPE

(c)LARGE

What is the difference between STDEV.S and STDEV.P? 1. (a)STDEV.S calculates the standard deviation of a sample; STDEV.P calculates the standard deviation of a population. 2. (b)STDEV.P calculates the standard deviation of a population; STDEV.S calculates average variation. 3. (c)STDEV.P calculates the standard deviation of a population; STDEV.S calculates variance. 4. (d)There is no difference.

(a)STDEV.S calculates the standard deviation of a sample; STDEV.P calculates the standard deviation of a population.

The Header & Footer Tools tab contains commands that insert codes into a header or footer. These codes enable the header or footer text to change automatically. You can insert a code for all of the following except: 1. (a)Your Name. 2. (b)File Name. 3. (c)Sheet Name. 4. (d)Page Number.

(a)Your Name.

Cell A2 contains the regular price $100. Cell B2 contains the discount rate 15%. Cell C3 contains =A2*(1-B2) to calculate the sale price of $85. Which of the following formulas produces the same result? 1. (a)=A2*(B2-1) 2. (b)=A2-(A2*B2) 3. (c)=A2*B2-1 4. (d)=A2*1-B2

(b)=A2-(A2*B2)

The date February 16, 2021 is stored in cell B1. The last day of the semester (May 7, 2021) is stored in cell B2. Which function calculates the number days until the end of the semester? 1. (a)=DAYS(B1,B2) 2. (b)=DAYS(B2,B1) 3. (c)=YEARFRAC(B1,B2) 4. (d)=DAY(B1)

(b)=DAYS(B2,B1)

Which function correctly adds data in cell D25 across multiple worksheets? 1. (a)=SUM(Monday,Friday:D25) 2. (b)=SUM(Monday:Friday!D25) 3. (c)=COUNT(Mon:D25,Tues:D25,Wed:D25,Thurs:D25,Fri:D25) 4. (d)=Monday:D25+Friday:D25

(b)=SUM(Monday:Friday!D25)

What is the difference between a sample and a population? 1. (a)A sample contains all data you want to evaluate, while a population contains a portion of data available. 2. (b)A population contains all data you want to evaluate, while a sample contains a portion of the data. 3. (c)A sample contains all data from the population except statistical outliers. 4. (d)There is no difference.

(b)A population contains all data you want to evaluate, while a sample contains a portion of the data.

What function would you use to calculate the strength of a relationship between two or more variables? 1. (a)STDEV.S 2. (b)CORREL 3. (c)STDEV.P 4. (d)FREQUENCY

(b)CORREL

You applied a chart style to a clustered column chart. However, you want to experiment with other settings to change the colors used in the chart without individually changing colors. Which of the following does not apply changes to the entire chart? 1. (a)Change the workbook theme. 2. (b)Change colors in the Format Data Series task pane. 3. (c)Select a color set from the Change Colors palette. 4. (d)Change the colors for the workbook theme.

(b)Change colors in the Format Data Series task pane.

You moved a chart to its own chart sheet. What chart element can you add to provide a grid that contains a copy of the data source at the bottom of the chart? 1. (a)Axis title 2. (b)Data table 3. (c)Gridlines 4. (d)Error bars

(b)Data table

Which step is not part of planning a worksheet design? 1. (a)Decide what input values are required. 2. (b)Enter labels, values, and formulas. 3. (c)State the purpose of the worksheet. 4. (d)Decide what outputs are required to achieve the purpose.

(b)Enter labels, values, and formulas.

When you start to type =AV, what feature displays a list of functions and defined names? 1. (a)Function ScreenTip 2. (b)Formula AutoComplete 3. (c)Insert Function dialog box 4. (d)Function Arguments dialog box

(b)Formula AutoComplete

Assume that the data on a worksheet consume a whole printed page and two columns on a second page. You can do all of the following except what to force the data to print all on one page? 1. (a)Decrease the Scale value 2. (b)Increase the left and right margins 3. (c)Decrease column widths 4. (d)Decrease the font size

(b)Increase the left and right margins

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? 1. (a)Show Axis in Thousands 2. (b)More Primary Vertical Axis Title Options 3. (c)Show Axis in Millions 4. (d)Show Right to Left Axis

(b)More Primary Vertical Axis Title Options

The function =PMT(C5,C7,-C3) is stored in cell C15. What must be stored in cell C5? 1. (a)APR 2. (b)Periodic interest rate 3. (c)Loan amount 4. (d)Number of payment periods

(b)Periodic interest rate

Which of the following functions should be used to determine how far the sample data is spread around the mean? 1. (a)COVARIANCE.P 2. (b)STDEV.S 3. (c)CORREL 4. (d)FREQUENCY

(b)STDEV.S

Which of the following is not true about the VLOOKUP function? 1. (a)The col_index_num argument cannot be 1. 2. (b)The lookup table must be in descending order. 3. (c)The default match type is approximate. 4. (d)The match type must be false when completing an exact match.

(b)The lookup table must be in descending order.

Which of the following is not a comparison operator? 1. (a)< 2. (b)> 3. (c)& 4. (d)<>

(c)&

Which of the following functions should be used to insert the current date and time in a cell? 1. (a)=TODAY() 2. (b)=CURRENT() 3. (c)=NOW() 4. (d)=DATE

(c)=NOW()

Why would Series 1 and Series 2 display in a chart legend? 1. (a)The data are contained in one column. 2. (b)The data are contained only in one row. 3. (c)Column headings were not selected when creating a chart. 4. (d)The selected headings are in non-adjacent cells to the values.

(c)Column headings were not selected when creating a chart.

What keystroke combination is required to calculate a Frequency data array? 1. (a)Ctrl+Enter 2. (b)Alt+Enter 3. (c)Ctrl+Shift+Enter 4. (d)Ctrl+Shift+Delete

(c)Ctrl+Shift+Enter

You have a workbook that contains sales data where each sales rep's data is stored on a separate worksheet. Which task is the least likely to be done while the worksheets are grouped? 1. (a)Fill the sales categories across the worksheets. 2. (b)Format the column and row labels at the same time. 3. (c)Enter specific values for the first sales rep. 4. (d)Format the values with an appropriate number style.

(c)Enter specific values for the first sales rep.

What is the keyboard shortcut to create an absolute reference? 1. (a)F2 2. (b)F3 3. (c)F4 4. (d)Alt

(c)F4

A dataset contains a list of U.S. Representatives, the states they represent, the years they were elected, and their salaries. You want to identify the highest-paid representative who was elected before 1/1/2020. What is the best function to use? 1. (a)MAX 2. (b)IFS 3. (c)MAXIFS 4. (d)YEARFRAC

(c)MAXIFS

If cell E15 contains the function =PMT(B$15/12,C7*12,-D8), what type of cell reference is B$15? 1. (a)Relative reference 2. (b)Absolute reference 3. (c)Mixed reference 4. (d)Syntax

(c)Mixed reference

A local police chief wants to create a rule that a person will be ticketed 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. Which function should be used? 1. (a)AND 2. (b)MAXIFS 3. (c)OR 4. (d)SWITCH

(c)OR

You want to copy the March worksheet to use it to enter data for April. After you right-click the March sheet tab and select Move or Copy, you click OK in the Move or Copy dialog box without changing any settings. What happens? 1. (a)A copy of the March sheet is inserted to the right of the original March sheet. The new sheet is named April automatically. 2. (b)A copy of the March sheet is inserted to the left of the original March sheet. The new sheet is named March (1). 3. (c)The March sheet is moved to the left of the first sheet tab. The worksheet is not copied. 4. (d)The March sheet is moved to a new workbook named Book1. The March sheet no longer exists in the original workbook.

(c)The March sheet is moved to the left of the first sheet tab. The worksheet is not copied.

You just copied a range of data containing formulas. However, you want to preserve the formula results and the original number and text formatting in the pasted range. Which paste option would you select? 1. (a)Formulas 2. (b)Keep Source Formatting 3. (c)Values & Source Formatting 4. (d)Values & Number Formatting

(c)Values & Source Formatting

You created a worksheet that tracks data for a basketball team. The range B10:F10 contains the difference in final points between your team and the other team. What is the recommended sparkline to create to represent this data? 1. (a)Line 2. (b)Column 3. (c)Win/Loss 4. (d)Plus/Minus

(c)Win/Loss

You want to create a single chart that shows the proportion of yearly sales for five divisions for each year for five years. Which type of chart can accommodate your needs? 1. (a)Pie chart 2. (b)Surface chart 3. (c)Clustered column chart 4. (d)100% stacked column chart

(d)100% stacked column chart

The date 8/3/2021 is stored in cell C1. What function is used to extract just 3? 1. (a)=YEAR(C1) 2. (b)=MONTH(C1) 3. (c)=DAYS(B1,C1) 4. (d)=DAY(C1)

(d)=DAY(C1)

Which of the following is not an appropriate use of the MAX function? 1. (a)=MAX(B3:B45) 2. (b)=MAX(F1:G10) 3. (c)=MAX(A8:A15,D8:D15) 4. (d)=MAX(D15-C15)

(d)=MAX(D15-C15)

What number format places a dollar sign on the left side of the cell, includes commas to separate thousands, displays two decimal places, and displays zero values as hyphens? 1. (a)Currency Format 2. (b)Monetary Number Format 3. (c)Dollars and Cents Format 4. (d)Accounting Number Format

(d)Accounting Number Format

If you want to show exact values for a data series in a bar chart, which chart element should you display? 1. (a)Chart title 2. (b)Legend 3. (c)Value axis title 4. (d)Data labels

(d)Data labels

The SalesQtr2 workbook contains links to data in the April, May, and June workbooks. Which workbook is the destination file? 1. (a)June 2. (b)April 3. (c)May 4. (d)SalesQtr2

(d)SalesQtr2

The label Souvenir Shop is in cell A1, and April Sales Report is in cell B1. You select the range A1:E1 and click Merge & Center. What is the result? 1. (a)Souvenir Shop is centered over the range A1:E1, and April Sales Report is moved to cell F1. 2. (b)Excel does not let you merge and center a range of cells where those cells each contain data. 3. (c)Souvenir Shop is combined with April Sales Report. The new combined label is then centered over the range A1:E1. 4. (d)Souvenir Shop is centered over the range A1:E1, and April Sales Report is deleted.

(d)Souvenir Shop is centered over the range A1:E1, and April Sales Report is deleted.

Cell A5 is the active cell. You want to make cell B10 the active cell. Which of the following is not a method for going to cell B10? 1. (a)Use the Go To dialog box. 2. (b)Type B10 in the Name Box and press Enter. 3. (c)Use the scroll arrows on the keyboard. 4. (d)Type B10 in the Formula Bar and press Enter

(d)Type B10 in the Formula Bar and press Enter

You 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 tool in Excel could you use to investigate the relationships between the data? 1. (a)CORREL 2. (b)PERCENTRANK.INC 3. (c)VAR.S 4. (d)RSQ

1. (a)CORREL

A combo chart displays the number of new jobs on the primary value axis, percentage growth on the secondary value axis, job titles in the category axis, "New Job Growth by 2026" as the chart title, but no legend. Which is the least important title to include in the chart? 1. (a)The chart title New Job Growth by 2026 2. (b)The primary value axis title Number of New Jobs 3. (c)The category axis Job Titles 4. (d)The secondary value axis title Percentage Growth

c)The category axis Job Titles

A worksheet contains over 400 rows. The first 10 rows contain input data, and functions containing summary statistics are in the last 10 rows. What is the most efficient method to see the input area and summary statistics at the same time without having to scroll back and forth between sections? 1. (a)Open multiple windows of the same workbook and use the Arrange Windows command to tile the windows. 2. (b)Display a Watch Window for the input section as you view the summary statistics section. 3. (c)Select the summary statistics and use the Freeze Panes option. 4. (d)Use the Split command and adjust one pane to see the input section and another pane to see the summary statistics.

d)Use the Split command and adjust one pane to see the input section and another pane to see the summary statistics.