Deck 7: Specialized Functions

Question 1

Which of these functions does not support multiple conditions?

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

Answer 1

D

Question 2

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)))

Answer 2

A

Question 3

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 3

B

Question 4

What is the first argument of an IF function?

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

Question 5

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

Question 6

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 7

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 8

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 9

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 10

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 11

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

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

Question 12

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 13

Which function returns the relative position of a value?

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

Question 14

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 15

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 16

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 17

Which is not an argument in an INDEX function?

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

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 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 20

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

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

Question 21

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

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

Question 22

What is < > equivalent to in Advanced Filters?

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

Question 23

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 24

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

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

Question 25

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

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

Question 26

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 27

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 28

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 29

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

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

Question 30

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

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

Question 31

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

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

Question 32

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 33

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 34

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 35

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 36

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 37

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 38

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 39

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 40

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 41

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

Question 42

The ________ function calculates the total future value.

Question 43

The ________ function returns the difference between 2 dates.

Question 44

The first argument of any database function is ________.

Question 45

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

Question 46

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

Question 47

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

Question 48

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

Question 49

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

Question 50

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

Question 51

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

Question 52

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

Question 53

The ________ database function will add the values on a specified 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

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

Question 57

The ________ function returns the position of a value.

Question 58

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

Question 59

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

Question 60

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

Question 61

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

Question 62

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

Question 63

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

Question 64

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

Question 65

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

Question 66

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

Question 67

Advanced filtering will not copy results to a new location.

Question 68

A MATCH function returns the value of a given position.

Question 69

An INDEX function returns the position of a value.

Question 70

A NOT function reverses the truth of a logical test.

Question 71

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

Question 72

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

Question 73

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

Question 74

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

Question 75

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

Question 76

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

Question 77

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

Question 78

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

Question 79

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

Question 80

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