BSAN IT CH 5
The IF function has ________ arguments
3
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? (1 point)
=IF(B7>20, "ORDER", "OK")
Which of the following is the correct syntax for the INDEX function?
=INDEX(array, row_number, [column_number])
The correct syntax for the SUMIF function is ________
=SUMIF(range, criteria, [sum_range])
If letters appear for the column headings, the reference style for Excel is____
A1
When a(n) ________is nested in the logical_test argument of an IF function, both logical tests need to be TRUE for the [value_if_true] argument to be returned.
AND
The third argument of the _______ function is only required if one range of data is being averaged based on the criteria of a second range.
AVERAGEIF
The______function expands on the AVERAGEIF function by allowing multiple criteria to determine the subset of data.
AVERAGEIFS
All of the following statements are TRUE about an Excel database EXCEPT: In a database, each record is one unit of data. All database functions are named using the format DBXXX() where XXX is the name of the corresponding nondatabase Excel function. All database functions include the same three arguments. An Excel database is a way of storing data that is made up of records and fields.
All database functions are named using the format DBXXX() where XXX is the name of the corresponding nondatabase Excel function.
Logical test
An equation that can be evaluated as either true or false
Which of the following is NOT a common logical function? COUNT OR NOT IF
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: (1 point) The IF function is the most common logical function. Cell references listed in the IF function must be capitalized. The [value_if_false] argument may be omitted from the IF function. The TRUE argument of the IF function is optional.
Cell references listed in the IF function must be capitalized.
A(n)_____ is the strategic advantage that a business has over its competition.
Competitive advantage
Which of the following is NOT a database function?
DCALC
Advantages of using the ________ function instead the SUMIFS function include criteria are changed in cells and there is no need to alter the function.
DSUM
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: (1 point) Elements such as named ranges and cell references should go inside quotes. Conditional aggregate functions are functions that consolidate or summarize a subset of data that has been filtered based upon one or more criteria. The criteria can be on the data that is being aggregated or on associated data. With an aggregate function, a range would still be provided, but the aggregation would only use cells that meet a given criteria.
Elements such as named ranges and cell references should go inside quotes.
f 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
Static data
Has been manually calculated
the _____ function is the most common logical function. (1 point)
IF
The ________ function is a useful tool for detecting an error and displaying a more user friendly message.
IFERROR
the ____ function is a useful tool for detecting an error and displaying something more user friendly than the default error 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
________ functions enable evaluation and choices to be integrated into an Excel spreadsheet.
Logical
The ________ function looks for a value within a range and returns the position of that value within the range.
MATCH
The______ function looks for a value within a range and returns the position of that value.
MATCH
The ________ functions provide added flexibility of multiple data ranges that can be located throughout an Excel worksheet.
MATCH and INDEX
The_____ function is appropriate when there is only one option that does not fit the criteria
NOT
________ is a tool used to understand the logic of conjunction functions where words are entered in the function structure.
Pseudocode
____helps you determine the structure of a problem before you develop the actual formula
Pseudocode
If numbers appear for the column headings, the reference style for Excel is
R1C1
IF function
Returns one of two values
Which of the following is NOT an example of a statistical function? AVERAGEIF COUNTIFS AVERAGEIFS SUMIF
SUMIF
The ________ function sums data and allows for more than one filtering criteria.
SUMIFS
Double prime symbol
Straight quotation mark
All of the following statements are TRUE about conjunction functions EXCEPT: (1 point) The OR function requires at least one of the arguments to be TRUE. The NOT function results in the opposite or reverse of a logical statement. The AND and OR functions cannot be used in the same IF function. The AND function requires all arguments to be TRUE.
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: (1 point) You can use to look up a value and then, using that value as a reference, return data that is associated with that value. 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. VLOOKUP functions are extremely valuable when working with tables where the data is in columns. The HLOOKUP function is more commonly used. than the VLOOKUP function.
The HLOOKUP function is more commonly used. than the VLOOKUP function.
All of the following statements are TRUE regarding the INDIRECT function EXCEPT: (1 point) The INDIRECT function has three arguments. The INDIRECT function can change a text string within a cell to a cell reference. The cell reference in the INDIRECT function reroutes to a new reference. The INDIRECT function's first argument is usually a cell reference or a text string.
The INDIRECT function has three arguments.
All of the following statements are TRUE about the SUMIF and SUMIFS functions EXCEPT: (1 point) The SUMIFS function allows for more than one filtering criteria. The SUMIF and SUMIFS arguments are very dissimilar. SUMIF functions are based on one criterion. The SUMIF and SUMIFS functions select values from a range of data based on criteria, and then add those values.
The SUMIF and SUMIFS arguments are very dissimilar.
Logical function
The most common include IF, AND, OR, and NOT
In the VLOOKUP function below, which of the following statements is NOT true? =VLOOKUP(A6,Shifts,5,FALSE) The content of A6 is compared to the contents of first column of the table array. The number in the third argument stands for how many columns exist in the table array. Shifts is the name assigned to the table array being used by the function. The contents of the fourth argument requires an exact match with the first argument.
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: (1 point) Nested IF functions permit more than two outcomes. Excel color codes parentheses to indicate which parentheses are paired. Each nested function needs a pair of parentheses. The outcome of a nested IF function is limited to numeric or text values.
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: (1 point) The syntax of the DSUM function is =DSUM(field, database, [criteria]) where criteria is optional. An advantage of using the DSUM function is that you can see the criteria on the worksheet and understand the calculation much easier. 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. The criteria can be modified on the worksheet and the result is updated automatically.
The syntax of the DSUM function is =DSUM(field, database, [criteria]) where criteria is optional.
a(n) _____ function is used to look up a value and then return data that is associated with that value.
VLOOKUP
The second argument of the IF function is ________
[value_if_true]
The first argument of the AVERAGEIFS function is ________
average_range
The third argument of the VLOOKUP function is ________
col_index_number
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
The function =IF(SUM(Trans_Qty)>E20,"Goal Met","Under Goal") is an example of a(n) _____ function
complex
A(n)____aggregate function incorporates calculation and a logical decision.
conditional
COUNTIF and AVERAGEIF are examples of ______ statistical functions.
conditional
AND, OR, and NOT are examples of Excel ________ functions.
conjunction
_____ functions enable linking or joining of functions or formulas.
conjunction
The ________ argument of the COUNTIF function is the logical statement that will determine which cells to count.
criteria
A(n)_____ tree allows you to break down potential decisions in a logical, structured format.
decision
A(n) ________ allows you to break down potential decisions in a logical, structured format.
decision tree
Quotes, shown as _____ symbols, 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. t/f
false
A nested IF requires one or more IF functions joined by an AND function. t/f
false
AND, OR, and NEVER are all examples of conjunction functions. t/f
false
If letters appear for the column headings, the reference style for Excel is currently R1C1. t/f
false
On a worksheet, if numbers appear as the column headings, then the reference style for Excel is A1 t/f
false
Outline-code uses the structure of functions but with wording that is for logical understanding. t/f
false
The INDIRECT function is valuable because it can change a cell reference to a text string. t/f
false
The NEVER function allows creation of logical statements in which it creates a reverse result. t/f
false
The ability to use MATCH and INDEX together is a powerful capability within Excel. t/f
false
The correct syntax for the COUNTIF function is: COUNTIF(criteria, logical_test). t/f
false
The syntax for the IFERROR function is =IFERROR(value, value_if_true) t/f
false
Using _____ makes creating formulas easier because you do not need to worry about adding absolute and mixed cell references
names ranges
A ________ IF function uses IF functions as arguments within another IF function
nested
A)n)_____ IF function increases the logical outcomes that can be expressed
nested
The symbols < and > are examples of logical _____
operators
A pair of _____ is needed around each nested function
parentheses
The ________ argument of the COUNTIF function lists the cells that will be counted.
range
The first argument of the AVERAGEIF function is ________
range
Data that is manually calculated and typed into a spreadsheet is called ____ data
static
Data that is manually calculated is known as ________ 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. t/f
true
Conditional aggregate functions summarize a subset of data that has been filtered based upon specific criteria. t/f
true
Conditional math functions include SUMIF and SUMIFS functions. t/f
true
Excel color codes parentheses for each function used in a nested function. t/f
true
If a value is in the third row and fourth column, the cell reference would be R3C4. t/f
true
The AVERAGEIFS function expands on the AVERAGEIF function by allowing multiple criteria to determine the subset of data. (1 point) t/f
true
The COUNTIFS function allows for multiple criteria in multiple ranges to be evaluated and counted. t/f
true
The DSUM database function calculates the sum based on filters within a criteria range. t/f
true
The IF function can employ other logical functions nested within as arguments. t/f
true
The IF function provides two outcomes, one when its logical test is true and one when its logical test is false. t/f
true
The MATCH function looks for a value within a range and returns the position of that value.
true
The OR function is used when any combination of logical tests has at least one TRUE outcome. t/f
true
The VLOOKUP and HLOOKUP functions are used to look up a value and return data that is associated with that value. t/f
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. t/f
true
