Deck 5: Complex Conditional and Retrieval Functions

Question 1

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

A)Conditional aggregate functions are functions that consolidate or summarize a subset of data that has been filtered based upon one or more criteria.
B)With an aggregate function, a range would still be provided, but the aggregation would only use cells that meet a given criteria.
C)Elements such as named ranges and cell references should go inside quotes.
D)The criteria can be on the data that is being aggregated or on associated data.

Answer 1

C

Question 2

The second argument of the IF function is ________.

A)[value_if_true]
B)[value_if_false]
C)[logical_test]
D)[criteria]

Answer 2

A

Question 3

Which of the following is NOT a common logical function?

A)IF
B)NA
C)OR
D)NOT

Answer 3

B

Question 4

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

A)The AND function requires all arguments to be TRUE.
B)The NOT function results in the opposite or reverse of a logical statement.
C)The OR function requires at least one of the arguments to be TRUE.
D)The AND and OR functions cannot be used in the same IF function.

Question 5

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

A)=IF((B3<5 AND C1>7),"Pass","Fail")
B)=IF(AND(B3<5,C1>7),"Pass","Fail")
C)=IF(AND(B3<5 OR C1>7),"Pass","Fail")
D)=AND(IF(B3<5,C1>7),"Pass","Fail")

Question 6

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

A)Logical
B)Comparison
C)Decision
D)Conditional

Question 7

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

A)Each nested function needs a pair of parentheses.
B)Excel color codes parentheses to indicate which parentheses are paired.
C)Nested IF functions permit more than two outcomes.
D)The outcome of a nested IF function is limited to numeric or text values.

Question 8

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?

A)=IF(B7<20, "ORDER", "OK")
B)=IF(B7<20, "OK", "ORDER")
C)=IF(B7>20, "ORDER", "OK")
D)=IF(B7>20, "OK", "ORDER")

Question 9

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

A)array
B)range
C)span
D)series

Question 10

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

A)Pseudo
B)Simulated
C)Text
D)Statement

Question 11

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

A)logical_test
B)rule
C)criteria
D)control

Question 12

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

A)integrated
B)complex
C)detailed
D)compound

Question 13

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

A)multiple
B)composite
C)nested
D)multipart

Question 14

A(n)________ advantage is the strategic advantage that a business has over its competition.

A)unfair
B)business
C)competitive
D)generated

Question 15

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

A)decision model
B)decision tree
C)true/false diagram
D)IF function diagram

Question 16

Data that is manually calculated is known as ________ data.

A)static
B)dynamic
C)retrieved
D)generated

Question 17

The IF function has ________ arguments.

A)2
B)3
C)4
D)5

Question 18

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

A)conjunction
B)combination
C)aggregate
D)union

Question 19

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

A)The [value_if_false] argument may be omitted from the IF function.
B)The IF function is the most common logical function.
C)The TRUE argument of the IF function is optional.
D)Cell references listed in the IF function must be capitalized.

Question 20

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

A)=IF(AND(C7>B3, D8<=5),"PAY","NO PAY")
B)=IF(OR(C7>B3, D8<=5),"PAY","NO PAY")
C)=IF(NOT(C7>B3),"PAY","NO PAY")
D)=IF((SUM(A1:A7)>7),"PAY","NO PAY")

Question 21

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

A)COUNT
B)COUNTA
C)COUNTIF
D)COUNTIFS

Question 22

Which of the following is the correct syntax for the INDEX function?

A)=INDEX(array, row_number, [column_number])
B)=INDEX(column_number, row_number, array)
C)=INDEX(array, column_number, [row_number])
D)=INDEX([column_number], row_number, array)

Question 23

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

A)The HLOOKUP function is the more commonly used than the VLOOKUP function.
B)The "V" in VLOOKUP stands for vertical and is used when your comparison values are located in a column or vertically to the left of the data that you want to find.
C)You can use to look up a value and then, using that value as a reference, return data that is associated with that value.
D)VLOOKUP functions are extremely valuable when working with tables where the data is in columns.

Question 24

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

A)YES
B)NO
C)TRUE
D)FALSE

Question 25

The first argument of the AVERAGEIFS function is ________.

A)average_range
B)criteria_range
C)criteria
D)logical_test

Question 26

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

A)IFMISTAKE
B)ISMISTAKE
C)IFERROR
D)IS ERROR

Question 27

The third argument of the VLOOKUP function is ________.

A)lookup_value
B)col_index_number
C)range_lookup
D)table_array

Question 28

The first argument of the AVERAGEIF function is ________.

A)average_range
B)range
C)criteria
D)logical_test

Question 29

The ________ function looks for a value within a range and returns the position of that value within the range.

A)INDEX
B)MATCH
C)INDIRECT
D)VLOOKUP

Question 30

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

A)An advantage of using the DSUM function is that you can see the criteria on the worksheet and understand the calculation much easier.
B)The DSUM function is a database function that is ideal for setting up a criteria range and then calculating the sum based on the filters within that criteria range.
C)The criteria can be modified on the worksheet and the result is updated automatically.
D)The syntax of the DSUM function is =DSUM(database, field, [criteria])where criteria is optional.

Question 31

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

A)An Excel database is a way of storing data that is made up of records and fields.
B)In a database, each record is one unit of data.
C)All database functions are named using the format DBXXX()where XXX is the name of the corresponding nondatabase Excel function.
D)All database functions include the same three arguments.

Question 32

Which of the following is NOT a database function?

A)DAVERAGE
B)DCOUNT
C)DMIN
D)DCALC

Question 33

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.

A)DBSUM
B)DATASUM
C)DSUM
D)DTSUM

Question 34

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

A)HLOOKUP
B)VLOOKUP
C)RLOOKUP
D)TLOOKUP

Question 35

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

A)COUNTIFS
B)SUMIF
C)AVERAGEIF
D)AVERAGEIFS

Question 36

The correct syntax for the SUMIF function is ________.

A)=SUMIF(criteria, range, [sum_range])
B)=SUMIF(criteria_range, criteria)
C)=SUMIF(criteria, criteria_range)
D)=SUMIF(range, criteria, [sum_range])

Question 37

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

A)SUM and AVERAGE
B)VLOOKUP and HLOOKUP
C)IF and COUNTIF
D)MATCH and INDEX

Question 38

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

A)The INDIRECT function can change a text string within a cell to a cell reference.
B)The INDIRECT function has three arguments.
C)The INDIRECT function's first argument is usually a cell reference or a text string.
D)The cell reference in the INDIRECT function reroutes to a new reference.

Question 39

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

A)The SUMIF and SUMIFS functions select values from a range of data based on criteria, and then add those values.
B)SUMIF functions are based on one criterion.
C)The SUMIF and SUMIFS arguments are very dissimilar.
D)The SUMIFS function allows for more than one filtering criteria.

Question 40

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

A)The content of A6 is compared to the contents of first column of the table array.
B)Shifts is the name assigned to the table array being used by the function.
C)The number in the third argument stands for how many columns exist in the table array.
D)The contents of the fourth argument requires an exact match with the first argument.

Question 41

The IF function can employ other logical functions nested within as arguments.

Question 42

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

Question 43

The MATCH and INDEX functions can be used to overcome the data range limitations of the VLOOKUP and HLOOKUP functions.

Question 44

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

Question 45

The NEVER function allows creation of logical statements in which it creates the opposite or reverse result.

Question 46

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

Question 47

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

Question 48

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

Question 49

The OR function is used when any combination of logical tests has at least one TRUE outcome.

Question 50

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

Question 51

Excel color codes parentheses for each function used in a nested function.

Question 52

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

Question 53

AND,OR,and NEVER are all examples of conjunction functions.

Question 54

VLOOKUP and HLOOKUP are two functions used to look up a value and return data that is associated with that value.

Question 55

The AVERAGEIFS function expands on the AVERAGEIF function,allowing multiple criteria to determine the subset of data.

Question 56

The VLOOKUP function is the more commonly used LOOKUP function.

Question 57

Conditional aggregate functions are functions that consolidate or summarize a subset of data that has been filtered based upon one or more criteria.

Question 58

The IF function provides two outcomes,one when its logical test is true and one when its logical test is false.

Question 59

The correct syntax for the COUNTIF function is: COUNTIF(range,logical_test).

Question 60

Conditional math functions include SUMIF and SUMIFS functions.

Question 61

The ________ function is the most common logical function.

Question 62

In the process of checking the values and eliminating errors,it is best to start with formulas that are simple and do not reference other cells that have formulas.

Question 63

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

Question 64

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

Question 65

The function =IF(SUM(Trans_Qty)>E20,"Goal Met","Under Goal")is an example of a(n)________ function.

Question 66

The MATCH function looks for a value within a range and returns the position of that value within the range.

Question 67

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

Question 68

A(n)________ is the strategic advantage that a business has over its competition.

Question 69

The symbols < and > are examples of logical ________.

Question 70

________ functions allow evaluation of multiple logical tests and enable linking or joining of functions or formulas.

Question 71

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

Question 72

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

Question 73

A pair of ________ is needed around each nested function.

Question 74

Quotes,shown as a(n)________ symbol,not the curly double quotation marks you use when writing papers,let Excel know that the element is a text string and not a numeric value,cell reference,or named range.

Question 75

The INDEX function works with the MATCH function extremely well because the MATCH function indicates the row where a match was found,and then the INDEX function can go to that row and another column to retrieve associated data.

Question 76

The foundation of a logical function is a logical ________ or logical expression.

Question 77

The syntax for the IFERROR function is =IFERROR(value,value_if_true)

Question 78

A(n)________ IF increases the logical outcomes that can be expressed.

Question 79

Using ________ makes creating formulas easier because you do not need to worry about adding absolute and mixed cell references.

Question 80

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