Deck 21: Specialized Functions

Question 1

Which of the following functions will calculate the total principal paid between select periods?

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

Answer 1

A

Question 2

When using the MATCH function with an exact match lookup_value,the match type must be set to:

A)1)
B)0)
C)-1.
D)2)

Answer 2

B

Question 3

Which function will calculate the periodic interest rate of a loan with a $500 payment on a $20000 loan paid back over 5 years?

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

Answer 3

B

Question 4

Which of the following functions will return the principal payment of a loan?

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

Question 5

Which function will calculate the current value on an investment that will earn 5% interest on yearly payments of $100 over 10 years?

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

Question 6

If there are four possible outcomes,how many logical tests are required?

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

Question 7

Which of the following functions will calculate the total interest paid between select periods?

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

Question 8

Which of the following logical functions will return a value if false if one of the arguments is false?

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

Question 9

Which database function will return the largest value from a dataset based on preset criteria?

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

Question 10

Which of the following functions does not support multiple conditions?

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

Question 11

Which function will return the total number of periods on a $20000 loan at 5% interest and a $600 payment?

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

Question 12

Which function will return the intersection of a specific row and column?

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

Question 13

Which of the following has the ability to restrict data view to only information that fits predetermined criteria?

A)VLOOKUP
B)Advanced Filter
C)HLOOKUP
D)INDEX

Question 14

Which function will calculate the final value of an investment of $100 a month that earns 4% interest over 5 years?

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

Question 15

Which of the following functions will return the interest payment of a loan?

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

Question 16

Which database function will return the smallest value from a dataset based on preset criteria?

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

Question 17

What is the first argument of the IF function?

A)Value_if_false
B)Value_if_true
C)logical_test
D)lookup_value

Question 18

Which of the following functions requires all arguments to be true in order to return the value if true?

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

Question 19

What is the second argument of the IF function?

A)Value_if_false
B)Value_if_true
C)logical_test
D)lookup_value

Question 20

Which of the following is a nested function?

A)=IF(AND(B7>5),"Yes","No")
B)=SUM(B6:B10)
C)=VLOOKUP(B8,A10:B20,2)
D)=HLOOKUP(B8,A10:B20,2)

Question 21

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

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

Question 22

Which of the following database functions is best suited to add total salaries from a database that are higher than $30,000?

A)DMIN
B)DSUM
C)DMAX
D)DCOUNTA

Question 23

Which of the following functions should be used to evaluate multiple conditions with only two possible outcomes?

A)AND
B)DSUM
C)IF
D)DMAX

Question 24

When using Advanced Filtering empty cells in the criteria range will return which of the following?

A)All records
B)No records
C)Select records
D)NA

Question 25

Which database function should be used to count records with empty cells?

A)DCOUNT
B)DCOUNTA
C)DGET
D)DSUM

Question 26

What is the maximum number of IF statements in a formula?

A)32
B)54
C)64
D)100

Question 27

Which of the following functions returns a value or the reference to a value within a range based on X
And Y coordinates?

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

Question 28

When using Advanced Filtering < > is the equivalent of:

A)equal to.
B)not equal to.
C)greater than.
D)less than.

Question 29

The first argument of the INDEX function is:

A)row_num.
B)array.
C)column_num.
D)match type.

Question 30

In the FV function,the rate argument refers to the:

A)total number of periods.
B)repayment type.
C)periodic rate.
D)payment.

Question 31

Which of the following functions has the ability to copy all results to a new location?

A)DSUM
B)DMIN
C)Advanced Filter
D)IPMT

Question 32

Which of the following is the best choice to lookup values when VLOOKUP and HLOOKUP are not possible?

A)LOOKUP
B)DGET
C)MATCH and INDEX
D)SUM

Question 33

In the function =IF(E5

A)Value_if_true.
B)Value_if_false.
C)Logical_test.
D)Modifier.

Question 34

Which of the following functions returns the position of a value in a list?

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

Question 35

Which of the following is not a function for evaluating multiple conditions with only two possible outcomes?

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

Question 36

When using Advanced Filtering,the criteria range must contain at least:

A)three rows.
B)one columns.
C)three columns.
D)two rows.

Question 37

When the DAVERAGE function returns #DIV/0:

A)there are no records that fit the criteria.
B)there is a typo in the function.
C)a second criteria is needed.
D)the incorrect function was used.

Question 38

=IF(E5

A)Database function.
B)Look Up function.
C)Advanced Filter.
D)Nested IF function.

Question 39

Returns the value or the reference to a value within a range based on X and Y coordinates.

A)DSUM
B)INDEX
C)IPMT
D)PPMT

Question 40

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

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

Question 41

PPMT should be used to calculate the specific interest paid on one installment of a loan.

Question 42

The FV function is best suited for calculating the current value of an investment.

Question 43

In order for the AND function to return a true value,all arguments must be true.

Question 44

The database argument of any database function requires the selection of the entire table.

Question 45

If the criteria of an advanced filter is blank no records will be returned.

Question 46

The MATCH function match_type must be set to 0 when matching words.

Question 47

If no values exist in the logical argument,the AND function returns the #VALUE! error.

Question 48

If an IF statement has four outcomes,it will require three logical tests.

Question 49

To perform an advanced filter with an OR condition you must select at least two rows of the criteria range.

Question 50

The MATCH function will return a value of 1 if the given lookup_value is the largest number in an array of numbers.

Question 51

The maximum number of IF statements in one formula is 64.

Question 52

CUMIPMT calculates the cumulative interest throughout a loan.

Question 53

CUMPRINC contains six arguments.

Question 54

In order for the OR function to return a true value,one or more arguments must be true.

Question 55

A PMT of zero can be used in the FV function.

Question 56

The CUMPRINC function requires both starting and ending evaluation dates.

Question 57

Advanced filtering will not copy results to a new location.

Question 58

In order for the NOT function to return a true value,all arguments must be false.

Question 59

The IPMT function should be used to calculate the specific interest paid on one installment of a loan.

Question 60

The criteria range of a database function must contain at least one column heading and one cell of information.

Question 61

________ will return the interest paid for one installment of a loan.

Question 62

The ________ function is best suited when two or more logical tests are required and all conditions must be true in order to return the value_if_true.

Question 63

Excel permits ________ IF statements in one formula.

Question 64

=INDEX(A2:
B5,MATCH(MAX(B2:
B5),B2:
B5,0),1)is an example of a nested function.

Question 65

________ will return the principal paid on one specific payment of a loan.

Question 66

The ________ should be used to calculate the periodic rate of a loan given the NPER,PMT,and PV.

Question 67

The ________ function is best suited when two or more logical tests are required and at least one condition must be true to return the value_if_true.

Question 68

The PV function would be best suited to calculate the future value of an investment.

Question 69

________ will return the total interest paid within a time range on a loan.

Question 70

The ________ function returns the position of a value in a list.

Question 71

The ________ function is best suited to return the highest numerical value of a range of values within a database.

Question 72

________ is the best database function for counting data that contains empty cells.

Question 73

The NPER function would be best used to determine the periodic interest rate of an investment.

Question 74

The ________ function should be used to calculate the interest paid on the first payment of a mortgage.

Question 75

All database functions require a user defined:

A)set cell.
B)criteria range.
C)target range.
D)filter.

Question 76

The MATCH type should be set to ________ when matching words with the MATCH function.

Question 77

The RATE function returns the periodic rate for an investment or loan.

Question 78

________ calculates the standard deviation of a sample population.

Question 79

The MATCH function can be used to match words.

Question 80

________ can copy filtered information to a new location.