Excel Exam 2 Chapter 5-8

The date 8/9/2024 is stored in cell C5, and =WEEKDAY (C5) is stored in cell D5. What is the default function result if cell D5 has not been formatted? A. 6 B. 9 C. Friday, August 9, 2024 D. Friday

A. 6

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 and if that person has two or more speeding violations on record. Which function should be used? A. AND B. MAXIFS C. OR D. SWITCH

A. AND

What tool calculates the value required in a single cell to produce a result within a related cell? A. Goal Seek B. Solver C. One- or two-variable data table D. Scenario Manager

A. Goal Seek

Which of the following is not depicted in a box and whisker chart? A. Median B. First quartile C. Fourth quartile D. Average

A. Median

Which of the following is not an acceptable name range? A. Purchase_Price B. APR C. Term D. Down Payment

A. Purchase_Price (NOTE: ANYTHING WITH AN UNDERSCORE LINE IS NOT ACCEPTABLE)

You created a PivotTable that shows total number of customers per day; however, you want to display the average number of customers per day. What would you do to change the PivotTable results? A. Select the Average calculation as a value field setting. B. Use the Subtotal feature to select the Average function. C. Move the number of customers field from the Values area to the Filters area in the PivotTable Fields task pane. D. Create a calculated field and then remove the PivotTable column

A. Select the Average calculation as a value field setting.

Which of the following is not used when creating a calculated field in a PivotTable? A. cell reference to a value outside of the PivotTable B. Operands, such as multiplication (*) C. Name of a field containing values D. Another value to be used in the calculation

A. cell reference to a value outside of the PivotTable

The date February 16, 2024, is stored in cell B1. The last day of the semester (May 7, 2024) is stored in cell B2. Which function calculates the number days between those dates? A. =DAYS(B1,B2) B. =DAYS(B2,B1) C. =YEARFRAC(B1,B2) D. =DAY(B1)

B. =DAYS(B2,B1)

What is the difference between a sample and a population? A. A sample contains all data you want to evaluate, while a population contains a portion of data available. B. A population contains all data you want to evaluate, while a sample contains a portion of the data. C. A sample contains all data from the population except statistical outliers. D. There is no difference.

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

A workbook contains a list of houses and the months that they were sold in several cities in Florida in June. You are interested in determining the average price of sold houses in Ft. Lauderdale. What function is best suited for this calculation? A. AVERAGEIF B. AVERAGEIFS C. SUMIF D. MAXIFS

B. AVERAGEIFS

What function would you use to calculate the strength of a relationship between two or more variables? A. STDEV.S B. CORREL C. STDEV.P D. FREQUENCY

B. CORREL

You created a PivotTable and changed some values in the dataset from which the PivotTable was created. How does this affect the PivotTable? A. The PivotTable is updated automatically when you make changes to the dataset. B. Changes in the dataset do not affect the PivotTable until you refresh the PivotTable. C. You must create a new PivotTable if you want updated results in a PivotTable. D. The PivotTable is deleted from the workbook because it is not up-to-date.

B. Changes in the dataset do not affect the PivotTable until you refresh the PivotTable.

You want to create a PivotTable that uses fields from two Excel tables. What is the first step?A. Create a PivotTable using one table and then add the second table to the PivotTable Fields task pane. B. Create a relationship on a common field between the two tables. C. Merge the two tables into one large dataset before creating the PivotTable. D. Create a subtotal for each table, copy and paste the two subtotals together, and then create the PivotTable.

B. Create a relationship on a common field between the two tables.

What is the keyboard shortcut to open the Paste Name dialog box? A. F2 B. F3 C. F4 D. F5

B. F3

Which function should you use to calculate the cumulative value at the end of 20 years in which you invest $100 every month with 4.5% APR? A. CUMIPMT B. FV C. NPV D. PV

B. FV

For an AND function to be true... A. Both logical tests must be false. B. One logical test must be true. C. One logical test must be true, and a second logical function must be false. D. Both logical tests must be true.

B. One logical test must be true.

For an OR function to be true... A. Both logical tests must be false. B. One logical test must be true. C. One logical test must be true, and a second logical function must be false. D. Both logical tests must be true.

B. One logical test must be true.

Which of the following functions should be used to determine how closely a linear trendline represents the data being evaluated? A. STEYX B. RSQ C. SLOPE D. INTERCEPT

B. RSQ

Which of the following tools can incorporate constraints? A. Goal Seek B. Solver C. Data Tables D. Scenario Manager

B. Solver

What does the square root of the variance calculate? A. Covariance B. Standard deviation C. Correlation coefficient D. Frequency

B. Standard deviation

Why would you click the collapse outline symbol above a column of outlined data? A. You want to see more detailed columns that relate to the formula in that column. B. You want to hide the detailed columns to focus on the result column. C. You want to insert a subtotal row for a set of rows. D. You want to delete columns containing values.

B. You want to hide the detailed columns to focus on the result column.

Which of the following constraint operators should be used to limit a constraint to only whole numbers? A. bin B. int C. dif D. =

B. int

A calculated field is a user-defined field that ______? A. calculates gain and losses. B. is created after the PivotTable is built. C. is created before the PivotTable is built. D. calculates the number of items in a PivotTable. E. A field with multiple data types.

B. is created after the PivotTable is built.

What is a histogram? A. A graph of change over time B. A chart where value ranges used as categories C. A bar chart D. A networking datagram E. A comparison of historical categories

C. A bar chart

You want to use an IFS function to indicate which gift card customers get based on the points they have accumulated in one month in the rewards program. Customers who spent over $500 get $50, customers who spent between $250-$499 get $25, and customers who spent $00-$199 get $10. The first customer's amount spent is in cell B5. If the first logical test is b5>=500, what is the second logical argument? A. B5<500 B. B5>250 C. B5>=250 D. 200>B5<500

C. B5>=250

What settings should you select to apply a different color scheme and display a fill color for every other row or horizontal lines within the PivotTable? A. Banded Rows and Banded Columns check boxes B. Banded Columns check box and a different PivotTable style C. Banded Rows check box and a different PivotTable style D. A different PivotTable style only

C. Banded Rows check box and a different PivotTable style

What keystroke combination is required to calculate a Frequency data array? A. Ctrl+Enter B. Alt+Enter C. Ctrl+Shift+Enter D. Ctrl+Shift+Delete

C. Ctrl+Shift+Enter

What tab displays the Scenario Manager? A. Home tab B. Formulas tab C. Data tab D. Analyze tab

C. Data tab

________ is the process of keeping rows and/or columns visible onscreen at all times even when you scroll through a large dataset. A. Holding. B. Locking. C. Freezing. D. Pinning

C. Freezing

In the linear equation y=mx+b, which function would you use to calculate b? A. SLOPE B. STYEX C. INTERCEPT D. RSQ

C. INTERCEPT

A dataset contains a list of nursing employees at a hospital. You want to give a $500 bonus to head ER nurses and nurse managers. Which function do you nest within an IF function to ensure all the nurses with either title get the bonus? A. AND B. NOT C. OR D. EITHER

C. OR

Which tool is most effective when comparing the impact of various combinations of production expenses and new customer acquisition on net sales profit? A. Goal Seek B. Solver C. One- or two-variable data table D. Scenario Manager

C. One- or two-variable data table

Which of the following functions can be used to calculate the standard error of a linear equation? A. CORREL B. RSQ C. STEYX D. SLOPE

C. STEYX

You used the Subtotal feature on a dataset containing states, major cities within each state, and populations. You intended to create one subtotal for each state; however, a subtotal row displays below each city. What caused this result? A. You filtered out the state names before using the Subtotal feature. B. The data was sorted by state and further sorted by city. C. The At a change in setting was City within the Subtotal dialog box. D. You forgot to engage the Auto Outline feature before usin

C. The At a change in setting was City within the Subtotal dialog box.

What is true of Goal Seek? A. You work forward, looking at the data present and determining the best answer. B. You work forward, attempting to identify the optimal solution. C. You work backward, starting at the outcome and backfill data to reach that outcome. D. You work backward, comparing multiple scenarios to identify the one that provides the best possible outcome.

C. You work backward, starting at the outcome and backfill data to reach that outcome.

Which of the following could not be used to produce a map chart in Excel? (NOTE: addresses are too specific. Needs to be state, city, county, country) A. California, Colorado, Connecticut B. Orange County, El Paso County, Fairfield County. C. 92806, 80123, 06101 D. 412 Elm Avenue, 700 S Logan Circle, 602 Cedar Street

D. 412 Elm Avenue, 700 S Logan Circle, 602 Cedar Street

Which of the following Data Analysis tools could be used to compare statistical difference between two or more sample means? A. Histogram B. Moving Average C. Correlation D. ANOVA

D. ANOVA

Which of the following is not an optimization capability of Solver? A. Value of B. Min C. Max D. Average

D. Average

When you create a clustered column PivotChart from a PivotTable, the field that was in the Rows area of the PivotTable Fields task pane creates what part of the PivotChart? A. Legend B. Value axis C. Filters D. Category axis

D. Category axis

Which of the following functions should be used to determine a forecast based on seasonal data? A. FORECAST.ETS B. FORECAST.LINEAR C. FORECAST.CONFINT D. FORECAST.SEASONALITY

D. FORECAST.SEASONALITY

A dataset contains a list of governors, the states they represent, the years they were elected, and their salaries. You want to identify the lowest-paid representative who was elected after 11/1/2024. What is the best function to use? A. MIN B. IFS. C. MAXIFS D. MINIFS

D. MINIFS

Which of the following is not a capability of the Name Manager? A. Create a new name range B. Edit a name range C. Delete a name range D. Paste a name range

D. Paste a name range

You constructed a loan amortization table with beginning balance, monthly payment, interest paid, principal repayment, and ending balance columns. If all the formulas are correct, which is the only column that shows an increase in value throughout the term of the loan? A. Beginning Balance B. Monthly Payment C. Interest Paid D. Principal Repayment

D. Principal Repayment

After creating a blank PivotTable, you click a check box for a field that contains categorical data, such as state names, in the PivotTable Fields task pane. Where does Excel place that field? A. Filters area B. Columns area C. Values area D. Rows area

D. Rows area

You created a PivotTable for a major department store that lists department names (Shoes) in the first column and total weekly sales in the second column. How can you exclude Sunday sales from being reflected in the second column? A. Remove the weekdays from the Columns area of the PivotTable. B. Create a calculated field with an IF function. C. Sort the PivotTable by weekday and then collapse the rows containing Sunday data. D. Set a filter for the Weekday field and deselect the Sunday check b

D. Set a filter for the Weekday field and deselect the Sunday check box in the filter menu.

Which what-if analysis tool is the best option for complex calculations requiring constrained optimization? A. Goal Seek B. Scenario Manager C. Data Tables D. Solver

D. Solver

What should be done before inserting subtotals into a table? A. Filter the data. B. Create a PivotTable. C. Create a PivotChart. D. Sort the data.

D. Sort the data

What does ANOVA stand for? A. Alpha Numeric Online Virtual Artifacts B. Analysis of Virtual Artifacts C. Artifacts of Nominal or Overt Variation D. Analysis of Veteran's Affairs E. Analysis of Variance

E. Analysis of Variance

True or False: A calculated field in a pivot table is present when the pivot table is created.

False

True or False: Goal Seek allows you to solve multiple unknown variables.

False

True or False: The Analysis ToolPak is enabled by default.

False

True or False: Creating a data model in Excel is similar to creating relationships between tables in Access.

True

True or False: Solver allows you to solve multiple unknown variables.

True

True or False: The SUMIF function is a nested logical function.

True