business analytics excel chapter 5

Which of the following is an example of a conditional aggregate function?

=IF((SUM(A1:A7)>7),"PAY","NO PAY")

Which of the following is the correct syntax when using the AND function within the IF function?

=IF(AND(B3<5,C1>7),"Pass","Fail")

If cell B7 contains a value that is greater than 20, and "ORDER" will be displayed when the logical test is TRUE, which of the following contains the correct IF function syntax?

=IF(B7>20, "ORDER", "OK")

If letters appear for the column headings, the reference style for Excel is ___

A1

All of the following statements are TRUE about an Excel database EXCEPT:

All database functions are named using the format DBXXX() where XXX is the name of the corresponding nondatabase Excel function.

Which of the following is NOT a common logical function?

COUNT

The ________ function allows for multiple criteria in multiple ranges to be evaluated and counted.

COUNTIFS

All of the following statements are TRUE about the IF function EXCEPT:

Cell references listed in the IF function must be capitalized

Which of the following is NOT a database function?

DCALC

The ________ function is a database function that is great for setting up a criteria range and calculating the sum based on the filters within that criteria range.

DSUM

All of the following statements are TRUE about conditional aggregate functions EXCEPT:

Elements such as named ranges and cell references should go inside quotes.

If the VLOOKUP range_lookup argument has the ________ value entered, an exact match with the lookup_value argument is required.

FALSE

The ________ function is used when the lookup_value argument checks the top row of the table_array.

HLOOKUP

The ___ function is the most common logical function

IF

The ________ function is a useful tool for detecting an error and displaying a more user friendly message.

IFERROR

The ___ function returns the value of an element in a table or array selected by the row and column numbers

INDEX

The ___ function can change a text string within a cell to a cell reference

INDIRECT

The ________ functions provide added flexibility of multiple data ranges that can be located throughout an Excel worksheet. match

MATCH

The ________ functions provide added flexibility of multiple data ranges that can be located throughout an Excel worksheet.

MATCH and INDEX

___ helps determine the structure of a problem before you develop the actual formula. Rough draft of a formula

Pseudocode

If numbers appear for the column headings, the reference style for Excel is

R1C1

Which of the following is NOT an example of a statistical function?

SUMIF

Data that is manually calculated is known as ________ data.

Static

All of the following statements are TRUE about conjunction functions EXCEPT:

The AND and OR functions cannot be used in the same IF function

All of the following statements are TRUE about the VLOOKUP function EXCEPT:

The HLOOKUP function is more commonly used. than the VLOOKUP function.

All of the following statements are TRUE regarding the INDIRECT function EXCEPT:

The INDIRECT function has three argument

All of the following statements are TRUE about the SUMIF and SUMIFS functions EXCEPT:

The SUMIF and SUMIFS arguments are very dissimilar.

In the VLOOKUP function below, which of the following statements is NOT true? =VLOOKUP(A6,Shifts,5,FALSE)

The number in the third argument stands for how many columns exist in the table array.

All of the following statements are TRUE about nested IF functions EXCEPT:

The outcome of a nested IF function is limited to numeric or text values.

All of the following statements are TRUE about the DSUM function EXCEPT:

The syntax of the DSUM function is =DSUM(field, database, [criteria]) where criteria is optional.

Conjunction function

a function that allows evaluation of multiple logical tests and enables linking or joining of functions or formulas

Conditional statistical function

a function that calculates on a subset of data that meets specified criteria

Nested IF

a function that used IF function as arguments within another IF function

A(n) ________ advantage is the strategic advantage that a business has over other businesses conducting business in the same industry.

competitive

The following function is an example of a(n) ________ function. =IF(SUM(H2:J7)>Total_Sales,.05,"No Commission")

complex

A ___ aggregate function incorporates calculation and a logical decision

conditional

A ___ enables linking or joining function or formulas

conjunction

AND, OR, and NOT are examples of Excel ________ functions

conjunction

The ________ argument of the COUNTIF function is the logical statement that will determine which cells to count

criteria

A(n) ________ allows you to break down potential decisions in a logical, structured format.

decision tree

Quotes, shown as a ___ symbol, let Excel know that the element they surround is a text string

double prime

A nested IF decreases the logical outcomes that can be expressed.

false

A nested IF requires one or more IF functions joined by an AND function.

false

If letters appear for the column headings, the reference style for Excel is currently R1C1.

false

Outline-code uses the structure of functions but with wording that is for logical understanding.

false

The INDIRECT function is valuable because it can change a cell reference to a text string.

false

The NEVER function allows creation of logical statements in which it creates a reverse result.

false

The ability to use MATCH and INDEX together is a powerful capability within Excel.

false

________ functions enable evaluation and choices to be integrated into an Excel spreadsheet.

logical

an equation that can be evaluated as either true or false

logical test

Using ___ makes creating formulas easier because you do not need to worry about absolute and mixed cell reference

named ranges

A ________ IF function uses IF functions as arguments within another IF function.

nested

The < >are examples of logical ____

operators

A pair of ____ is needed around each nested function

parentheses

________ is a tool used to understand the logic of conjunction functions where words are entered in the function structure.

pseudocode

The ________ argument of the COUNTIF function lists the cells that will be counted.

range

Data that is manually calculated and typed into a spreadsheet is called _____ data

static

A conditional ___ function can calculate on a subset of data that meets specified criteria

statistical

The foundation of a logical function is a logical ____ or logical expression

test

At least one logical test must be included for the AND function

true

Conditional aggregate functions summarize a subset of data that has been filtered based upon specific criteria

true

If a value is in the third row and fourth column, the cell reference would be R3C4.

true

The DSUM database function calculates the sum based on filters within a criteria range.

true

The VLOOKUP function is the more commonly used LOOKUP function.

true

When eliminating errors, you should begin with formulas that are simple and do not reference other cells that have formulas

true