QBA Quiz Answers

The cell range $A$2:$E$30 is used to define the table_array argument of an HLOOKUP function. Which ofthe following cell ranges show where the function will search for the lookup value?

A2:E2

Consider the following formula: A4^D10+A3-(A9+B7)*C9/A4. Which of the following computations will happen first?

A9+B7

The cell range $A$2:$E$300 is used to define the table_array argument of a VLOOKUP function. If thecol_index_num argument is defined with the number 1, data from what column will appear as the output of thefunction when a match is found for the lookup value?

A

Which of the following best explains the difference between a Note and a Comment?

A Note contains a brief description or message. A Comment produces a thread where multiple users of aworkbook can enter information about a specific cell.

Which of the following is true with regards to conducting calculations with the data on a PivotTable?

A calculated field must be created by clicking the Fields, Items, and Sets button in the PivotTable Analyze tab of the Ribbon.

Which of the following is true when working with an Excel worksheet that was created by another person?

A data audit must be conducted before using an Excel Worksheet that was created by another person

Which of the following is true when working with an Excel worksheet that was created by another person?

A data audit must be conducted before using an Excel worksheet that was created by another person.

Which of the following is true with respect to a Note that is added to a cell on an Excel worksheet?

A red triangle will appear in the upper right corner of the cell location that contains a Note.

The residual value of a lease would be used to define which of the following arguments for the PMT function tocalculate monthly lease payments?

(FV)

A formula needs to be entered into cell C2 on an Excel worksheet that is being used as a template. The goal ofthe formula is to divide the value in cell A2 by the value in cell B2. The number entered into cell A2 is 20,000and the number entered into cell B2 is 100. Which of the following shows how this formula should be enteredinto cell C2?

A2/B2

Which of the following is a collection of investments managed by a financial professional and traded on the openmarket?

ETF

The following AVERAGEIFS function was entered into a cell in the first row of the Summary worksheet in an Excel workbook to calculate the arithmetic mean of a dataset that begins in row 5 and continues to row 15:=AVERAGEIFS($G$5:$G$15,A1,$J$5:$J$15,"10/28/2021")

Excel will not allow the function to be entered because the average_range argument is not defined.

A person invests $10,000 in bonds that provide annual interest payments of 5%. Which of the following can be used to calculate how much this investment will be worth in 10 years?

FV

The mouse pointer will turn into a black plus sign when it is dragged over the:

Fill Handle

Which of the following will help navigate and analyze a large dataset with many columns and/or rows?

Freeze Panes

Which of the following functions can be used to identify the highest value in a range of cells?

MAX

Which of the following functions can be used to identify the highest value in a range of cells?

Max

The following was entered into an Excel worksheet: =SUM(B5,B15). Which of the following is true with this entry?

Only values in cell B5 and B15 will be summed

Which of the following functions can be used to calculate monthly mortgage payments for a house?

PMT

Which of the following functions can be used to calculate monthly payments for a lease?

PMT

Which of the following functions can be used to calculate how much money should be invested today given anexpected rate of return and specified time frame in order to achieve a target value in the future?

PV

What is the center area of a chart that displays data and labels based on the type of chart that is being created?

Plot Area

Using data analytics to predict what might happen in the future is called

Predictive Analytics

Which of the following can be used as a data internal control that prevents a user from changing or deletingformulas and functions while using an Excel worksheet?

Protect Sheet

Which of the following Ribbon tabs contains the Spell Check command?

Review

Which of the following commands can be used to save an Excel workbook to the cloud using OneDrive

Save As

A grid background

Should be avoided or muted

Which of the following arguments of the VLOOKUP function is used to look for an exact match to the lookup_value?

[range_lookup]

Which of the following is an investment made in regular installments over a period of time?

annuity

When conducting a data audit using a PivotTable, which of the following should be identified in the underlying dataset?

columns that contain repeating values

When entering numeric data into an Excel worksheet, you should omit any symbols such as commas or dollar signs because:

it slows down the data entry process

The terms of a lease would be used to define which of the following arguments for the PMT function to calculate monthly lease payments.

nper

Which of the following arguments of the VLOOKUP function is used to define where the function will search for the lookup value?

table_array

Which of the following is a valid reason for using the Paste Formulas command?

when it is necessary to only paste a formula and not any formats that were applied to the cells that were copied

The cell range $A$2:$E$30 is used to define the table_array argument of an HLOOKUP function. If the row_index_num argument is defined with the number 10, data from what row will appear as the output of the function when a match is found for the lookup value?

11

Which of the following commands is used to center data horizontally in a cell?

Center

The number 10 is typed into the following cell locations: B2, B3, B4, and B5. The following is typed into cellC7: =COUNT(B2:B5). Which of the following values will be displayed in cell C7?

4

Charles Minard's graphic analysis of Napolean's Russian campaign displays data in how many dimensions?

6

The following is entered into cell C2: $B$2. If cell C2 is copied and pasted into cell E3 which of the following will appear when cell E3 is double clicked.

=$B$2

A function is being entered into an Excel worksheet to evaluate the contents in cells B5, C5, and D5. Thefunction needs to determine if the value in cell B5 is less than 2%, C5 is greater than 50, and if the word BUYis entered into cell D5. Which of the following functions can accomplish this goal?

=AND(B5<.02,C5>50,D5="BUY")

A person is saving money in a retirement account. At the end of each month, $500 is invested in an ETF that isexpected to earn an annual rate of return of 6%. Which of the following functions will show how much theretirement account will be worth after 20 years? Assume the values are entered into the following cells on anExcel worksheet: B1:$500 B2:6% B3:20

=FV(B2/12,B3*12,-B1)

A function is being entered into an Excel worksheet to evaluate the contents in cells B5, C5, and D5. The function needs to determine if the value in cell B5 is less than 2%, C5 is greater than 50, or if the word BUY is entered into cell D5. Which of the following functions can accomplish this goal?

=OR(B5<2%,C5>50,D5="BUY")

A PivotTable is created using a dataset that contains a column of transaction numbers related to each retail store. Instead of summing the transaction numbers for each store, the project requires that the transaction numbers arecounted for each store. Which of the following explains how this is accomplished?

After the field containing the transaction numbers is added to the VALUES area of the PivotTable, click the Field Settings button in the PivotTable Analyze tab of the Ribbon and change the output to COUNT.

Which of the following is found in the Data Analytics Model?

All of the above

Which of the following is true when creating a VLOOKUP function that will be copied and pasted to othercell locations in a worksheet?

An absolute reference must be applied to the cell range used to define the table_array argument.

Which of the following is the last step of the DATA Analytics framework?

Analysis

Creating a diagram to better understand a process or practice that is related to a data analytics project is helpfulfor which component of the Data Analytics model?

Analysis component of the DATA Analytics Framework

To apply the bold font to an activated cell, hold the CTRL key and tap which of the following keys?

B

Anscombe's quartet demonstrates that

Basic statistical measures can hide as much as they reveal

When entering settings into the Goal Seek dialog box, the cell reference used in a formula or function would beentered into which of the following?

By changing cell

Redundancy

Can give context and order to complexity

After clicking a field name in the VALUES area of a PivotTable, the Field Settings dialog box can be opened bywhich of the following?

Click the Field Settings button in the PivotTable Analyze tab of the Ribbon

Assume a dataset is being sorted using a column that contains the months of the year. If the data is going to be sorted in calendar year order (January to December), what should be selected from the Order dropdown box in the Sort dialog box?

Custom List

Which of the following is NOT one of the components of the DATA Analytics Framework?

Data Design

_____ is the way in which data is presented or visualized to an audience to explain circumstances and decisions in professional settings.

Data Presentation

A Treemap chart is an example of which data analytics technique?

Data Visualization

The sort command is found in which of the following combinations?

Data tab, Sort Button

The sort command is found in which of the following combinations?

Data tab, Sort button

Which of the following is the first step of the DATA Analytics framework?

Decision

Which of the following is one of the components of the DATA Analytics Framework?

Decisions

Which of the following is NOT a Principle of Graphical Excellence

Graphical presentations are as a rule preferred over simple presentations of data tables

Graphical "ducks" are

Graphs with more decoration than data

Which tab of the Ribbon contains commands to change the color of cells or text on a worksheet?

Home

Which of the following is where you would find commands to hide or unhide a column?

Home Tab Format Button

Which of the following is where you would find commands to hide or unhide a column?

Home tab, Format Button

To apply the Italics font to an activated cell, hold the CTRL key and tap which of the following keys?

I

A function is entered into an Excel worksheet to evaluate the values in cells C4 and D4. If the value in C4 isgreater than 10 the output of the function should be the word OK. If the output in cell D4 is greater than orequal to 2, the output should also be the word OK. Otherwise the output should read LOW. Which of thefollowing IF functions will provide an accurate output given the requirements provided?

IF(OR(D4>=2,C4>10),"OK","LOW")

Which of the following is NOT characteristic of Tufte's approach?

If you have to explain it, don't use it

Which of the following Ribbon tabs would be used to add a header or footer to a worksheet?

Insert

The data-ink ratio

Is the proportion of a graphic's ink devoted to the non-redundant display of data-information

When entering numeric data into an Excel worksheet, you should omit any symbols such as commas or dollar signs because:

It slows down the data entry process

When using the IF function to show only the first instance of each repeating value in a dataset, what must be done first?

Sort the entire dataset such that all descriptive columns are in a logical sequence.

The following was entered into a cell on an Excel worksheet: =TEXT(B15,mmm). Which of the following explains why the output of this function is the #NAME? error code?

The code entered in the format_text argument is not enclosed in quotations.

The following function was entered into cell A10 on an Excel worksheet and will be copied and pasted into

The column index number is too high

A home buyer is purchasing a home that costs $250,000. Cash in the amount of $50,000 was paid. A loan will be used to pay for the remaining cost of the house. Based on this information, which of the following is true?

The down payment is $50,000

If the [type] argument in the PMT function is defined with the number 1, which of the following is true?

The function will assume that payments will be made at the beginning of each period.

Which of the following is true with respect to the COUNTA function?

The function will count the number of cells that are not blank

The following SUMIFS function was entered into a cell on the Summary worksheet in an Excel workbook:=SUMIFS(Detail!C5:C15,Detail!A5:A15,Summary!A6,Detail!D5:D15,">"&Summary!E2)Which of the follow is true with regards to this function?

The function will produce an output by summing values in the range C5:C15 on the Detail worksheet basedon the defined criteria.

Which of the following explains the purpose of the WEEKNUM function?

The function will show the week number of a calendar year associated with a date serial number.

Which of the following are true with respect to the grand totals displayed for columns and rows on a PivotTable?

The grand totals are a permanent feature on a Pivot Table and cannot be removed

Which of the following are true with respect to the grand totals displayed for columns and rows on a PivotTable?

The grand totals can be removed by clicking the Grand Totals button in the Design tab of the Ribbon and making the desired selection.

The "Lie Factor" refers to

The ratio of the size of the effect in the graphic to the size of the effect in the data

An Excel workbook contains three worksheets: Data, Report, and Reference. The following was entered intocell A5 in the Report worksheet: =Data!C2Which of the following is true with regards to this entry?

The value entered into cell C2 on the Data worksheet will appear in cell A5 on the report worksheet.

To apply the underline format to an activated cell, hold the CTRL key and tap which of the following keys?

U

In time-series of money

Units should be deflated and standardized

A conditional format will be applied to cell B10 if the value in cell B10 is greater than 10. Which of the following should be selected from the New Formatting Rule dialog box in order to accomplish this goal?

Use a formula to determine which cells to format.

A dataset contains the following columns: Year, Month, and Total Sales Dollars. If a PivotTable is going to be created to sum the sales dollars by year, the Total Sales Dollars field should be dragged into which area of the PivotTable?

VALUES

A dataset contains the following columns: Year, Month, and Total Sales Dollars. If a PivotTable is going to becreated to sum the sales dollars by year, the Total Sales Dollars field should be dragged into which area of thePivotTable?

VALUES

The following SUMIF function was entered into a cell on an Excel worksheet:

Values in the range G2:G15 on the Detail worksheet will be summed.

The following warning was found on a worksheet that contains a PivotTable: "This PivotTable ONLY includes data from the range A2:C26 in the Sales Data worksheet." This is an example of a:

Warning Data Internal Control