Deck 24: Excel: Specialized Functions

Question 1

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

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

Answer 1

C

Question 2

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

Answer 2

B

Question 3

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)

Answer 3

A

Question 4

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)

Question 5

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

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

Question 6

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

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

Question 7

What is the second argument of the IF function?

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

Question 8

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

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

Question 9

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

What is the first argument of the IF function?

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

Question 13

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 14

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

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

Question 15

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

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

Question 16

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 17

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

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

Question 18

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 19

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

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

Question 20

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

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

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

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

Question 23

The first argument of the INDEX function is:

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

Question 24

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 25

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 26

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

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

Question 27

=IF(E5

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

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

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 30

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 31

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

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

Question 32

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 33

In the function =IF(E5

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

Question 34

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 35

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

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 38

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 39

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

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

Question 40

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

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

Question 41

The CUMPRINC function requires both starting and ending evaluation dates.

Question 42

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

Question 43

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

Question 44

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

Question 45

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

Question 46

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

Question 47

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

Question 48

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

Question 49

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

Question 50

CUMPRINC contains six arguments.

Question 51

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

Question 52

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

Question 53

CUMIPMT calculates the cumulative interest throughout a loan.

Question 54

Advanced filtering will not copy results to a new location.

Question 55

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

Question 56

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

Question 57

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

Question 58

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

Question 59

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

Question 60

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

Question 61

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

Question 62

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

Question 63

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

Question 64

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

Question 65

All database functions require a user defined:

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

Question 66

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 67

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

Question 68

________ can copy filtered information to a new location.

Question 69

The MATCH function can be used to match words.

Question 70

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

Question 71

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

Question 72

________ calculates the standard deviation of a sample population.

Question 73

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

Question 74

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

Question 75

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

Question 76

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

Question 77

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

Question 78

Excel permits ________ IF statements in one formula.

Question 79

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 80

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