Deck 7: Specialized Functions

Question 1

Which function returns a value at the intersection of a specified row and column?

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

Answer 1

B

Question 2

A(n)________is a matrix that provides the results (TRUE or FALSE)for every possibility of an AND,OR,or NOT criteria combination.

A)AND/OR/NOT tool
B)truth table
C)goal table
D)evaluation matrix

Answer 2

B

Question 3

Which is the Array argument in =INDEX(A2:
B5,MATCH(MAX(B2:
B5),B2:
B5,0),1)?

A)MAX(B2:B5)
B)MATCH(MAX(B2:B5),B2:B5,0)
C)1
D)A2:B5

Answer 3

D

Question 4

What is the purpose of the Row_num argument in the following INDEX function?
=INDEX(A2:
B5,MATCH(MAX(B2:
B5),B2:
B5,0),1)

A)To return the highest value in the range B2:B5
B)To return the position of the highest value in the range B2:B5
C)To return the column to the left of the highest values in the range B2:B5
D)To return the row of the highest value in the range B2:B5

Question 5

If you need to find the day of the month on which events occurred from a list of dates in the named range of Start_Date,which formula would provide the correct result?

A)=DAYS(End_Date,Start_Date)
B)=DAYMONTH(Start_Date)
C)=IF(DAYMONTH(Start_Date)<31,Start_Date,1)
D)=DAY(Start_Date)

Question 6

Why would you nest MATCH in an INDEX function?

A)To handle multiple outcomes
B)To find the highest or lowest value in an array
C)To use the position returned in the Match function as an argument in the index function
D)To use the value returned in the Match function as a lookup in the index function

Question 7

Which of these functions does not support multiple conditions?

A)NOT
B)OR
C)AND
D)MATCH

Question 8

What type of match would the following function return when the lookup array is sorted in descending order?
=MATCH(A3,B7:
B53,-1)

A)The match would be the greatest value in the list that is less than or equal to the value of A3.
B)The match would be an exact match to A3.
C)The match would be A3-1.
D)The match would be the smallest value in the list without going over the value of A3.

Question 9

If you need to find out how many days between two dates,which function would you use?

A)DAY
B)DAYS
C)DAYSBETWEEN
D)EDATE

Question 10

Which is not an argument in an INDEX function?

A)Array
B)Row_num
C)Table
D)Column_num

Question 11

The YEARFRAC calculates ________.

A)the year 2018 from a date
B)the number of years between two dates
C)the serial number of the year
D)the fraction of a year in days between two dates

Question 12

When formated as a date,the results of EDATE(Start_Date,3)if the contents of Start_Date is 10/30/2018 would be ________.

A)11/2/2018
B)1/31/2019
C)1/30/2019
D)10/31/2018

Question 13

Which function returns the relative position of a value?

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

Question 14

Which function calculates the Reduction in C2 to be filled through the rest of the reduction column if silver members have a 5% discount,gold 10% and Platinum have 20%?

A)=IF(A2="Silver",B2*0.05,IF(A2="Gold",B2*0.1,B2*0.2))
B)=IF(A2="Silver",B2*0.1,B2*0.2)
C)=IF(A2="Silver" B2*0.05,OR(A2="Gold",B2*0.1,OR(B2*0.2))
D)==IF(A2="Silver",B2*0.05,IF(A2="Gold",Platinum=B2*0.1,B2*0.2)))

Question 15

What is the first argument of an IF function?

A)Value_if_false
B)Logical_test
C)Value_if_true
D)Lookup_value

Question 16

IF you use the function,=MATCH(66,A1:
A4,0),on the table below,what would be the result?

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

Question 17

4)If there are 5 possible outcomes,how many logical tests are required?

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

Question 18

What is a nested function?

A)A function with more than one argument
B)Any IF function
C)A function with more than two arguments
D)A function embedded within an argument of another function

Question 19

What best describes a criteria range?

A)The table upon which a filter is applied
B)The group of adjacent cells that specifies the conditions used to control the results of a filter
C)The filtered range of cells
D)The copied range of cells

Question 20

If 9/3/2021 is the contents of B3,what would be the result of =MONTH(B3)?

A)44442
B)September
C)9/30/2021
D)9

Question 21

What is the purpose of an Excel What-if scenario?

A)A two-variable goal seek tool
B)Any what-if analysis
C)A tool that changes one variable through several iterations
D)Detailed set of values that represent different possible situations

Question 22

Which of these is not a capability of the Advanced Filter tool?

A)Filter in place
B)Copy selected records to a new location
C)Provide pop up filters
D)Specify the list range

Question 23

The advanced filter tool can ________.

A)delete duplicate records
B)search for unique records,only
C)not filter multiple arrays
D)filter a limit of 256 rows

Question 24

When using advanced filtering,leaving a cell empty in the criteria range will return which of the following?

A)All records
B)No records
C)Empty records
D)#N/A

Question 25

A(n)________ is a schedule that calculates the interest per payment period,principal,repayment,and remaining balance.

A)database
B)financial function
C)IPMT function
D)loan amortization table

Question 26

Which function calculates the total principal through a specified number of payments?

A)IPMT
B)CUMPRINC
C)CUMIPMT
D)NPER

Question 27

Which financial function calculates the number of payment periods for an investment or loan given fixed interest rate,periodic payment,and present value?

A)NPER
B)RATE
C)PV
D)IPMT

Question 28

Which of the following are not arguments for the IPMT function?

A)Rate
B)Per
C)Collection
D)Nper

Question 29

Which of the following is not an argument in a Database function?

A)Database
B)Criteria
C)Filter
D)Field

Question 30

Which function would calculate a final value of an investment of $250 per month for 5 years at 4% interest?

A)NPV
B)IPMT
C)PV
D)FV

Question 31

What effect does paying an extra small bit of principal on a mortgage have on the life of a 30 year loan?

A)It has no impact on amount of interest paid but shortens the length of the loan.
B)It saves money in interest and shortens the length of the loan.
C)It increases the amount of interest paid and lengthens the length of the loan.
D)It has no impact on either interest or life span of the loan.

Question 32

Which financial function calculates the net present value of an investment,given a fixed discount rate?

A)PV
B)FV
C)NPV
D)IPMT

Question 33

Which function calculates the total amount of interest paid over a specific number of payments?

A)CUMPMT
B)IPMT
C)CUMIPMT
D)PMT

Question 34

Which database function returns the lowest value from the dataset based on your criteria?

A)DMIN
B)DMAX
C)DLEAST
D)DCOUNT

Question 35

You need to know how many clients you have in a dataset with these fields in these ranges.Criteria are in A363:
F365.

Which function would accomplish this task?

A)DCOUNT(A6:F357,A3,A363:F365)
B)DCOUNT(A6:F357,A365:F363:G365)
C)DMIN(A6:F357,G365,A363:F365)
D)DMAX(A6:F357,G365,A363:F365)

Question 36

What is < > equivalent to in Advanced Filters?

A)Equal to
B)Not equal to
C)Greater than
D)Less than

Question 37

Why would you put a negative sign in many financial functions?

A)Excel requires a negative interest rate.
B)Excel interprets these calculations as a negative cash flow (money leaving your account).
C)Excel uses negatives for all financial functions.
D)Excel doesn't differentiate between income and outflow.

Question 38

Which function calculates a periodic rate for an investment or loan given the number of payments,fixed periodic payments,and present value?

A)IPMT
B)RATE
C)NPER
D)PV

Question 39

What function returns the total present value of an investment with a fixed rate,specified number of payment periods and a series of identical future payments?

A)PV
B)PMT
C)IPMT
D)FV

Question 40

The ________ function calculates the principal payment for a specified period on a loan or investment.

A)PPMT
B)IPMT
C)PMT
D)NPER

Question 41

A(n)________ is a group of two or more adjacent cells that specifies the conditions used to control the results of a filter.

Question 42

The YEAR function would return ________ on the value December 27,2025.

Question 43

The ________ function calculates the periodic interest rate of a loan or investment.

Question 44

If your database field contains empty cells,you should use the ________ function to count it.

Question 45

The ________ database function will add the values on a specified criteria range.

Question 46

In database functions,<>,>,=,<,>= and <= are called ________.

Question 47

The ________ function returns the position of a value.

Question 48

The ________ function evaluates two or more conditions and returns true only if ALL conditions are met.

Question 49

The ________ function returns the difference between 2 dates.

Question 50

What is the match_type argument value for an exact match in a MATCH function?

Question 51

The formula = ________ (10/15/2018,2)would return 12/15/2018.

Question 52

If you have 3 possible outcomes in a nested IF statement,then you need ________ logical tests.

Question 53

The ________ function identifies the highest value in a database column that matches specified conditions in a criteria range.

Question 54

The ________ function calculates the total number of periods in a loan.

Question 55

________ can copy filtered information to a new location.

Question 56

The first argument of any database function is ________.

Question 57

The ________ function calculates the periodic interest for a specified payment period on a loan or an investment.

Question 58

The ________ function calculates the total future value.

Question 59

The criteria range must contain ________ at least rows in Advanced Filtering.

Question 60

The ________ function calculates the current value of an investment given a fixed discount rate.

Question 61

An OR condition in an advanced filter requires at least two rows in the criteria range.

Question 62

The Arguments of an INDEX function are array,row_num,and column_num.

Question 63

A MATCH function returns the value of a given position.

Question 64

An empty cell in the criteria in an Advanced Filter will display no records.

Question 65

The ________ function calculates cumulative principal through a specified payment period.

Question 66

The real functionality of MATCH and INDEX functions comes from nesting them.

Question 67

You can nest AND,OR and NOT functions in an IF function.

Question 68

The EOMONTH function returns the last day of the month of the specified number of months from a given date.

Question 69

You need the same number of logical tests as outcomes in a nested IF function.

Question 70

An OR function evaluates to TRUE only if all of the conditions are true.

Question 71

The MONTH function would return 25 when used on a cell containing August 25,2018.

Question 72

The ________ function calculates the principal payment for a specified period on a loan or investment.

Question 73

A NOT function reverses the truth of a logical test.

Question 74

The ________ function calculates the periodic interest for a specified payment period on a loan or investment.

Question 75

The YEARFRAC function returns the number of the month in a year.

Question 76

An INDEX function returns the position of a value.

Question 77

A Truth Table is a matrix that provides true or false for every possible combination for an OR,NOT,or AND criteria combination.

Question 78

A(n)________ is a schedule that calculates the interest,principal repayment,and remaining balance of a loan.

Question 79

The ________ function calculates the cumulative interest through a specified number of payments.

Question 80

Advanced filtering will not copy results to a new location.