Deck 12: Excel 2013 : Analyzing Data Using Formulas

Question 1

If a value in a formula is not available,the error value #VALUE! will display in the cell.

Answer 1

False

Question 2

To name a range,select the range and then click the Define Name button in the Defined Names group.

Answer 2

True

Question 3

Names that you assign to cells and cell ranges can be used in formulas.

Answer 3

True

Question 4

What is the calculated result of the formula =PROPER("john smith")?

A) JOHN SMITH
B) John Smith
C) john smith
D) John smith

Question 5

Symbols,such as less than (<),greater than (>),and equal to (=),are all examples of comparison operators.

Question 6

A common example of a text delimiter is ____.

A) ,
B) =
C) +
D) (

Question 7

The symbol < > stands for "equal to."

Question 8

You can link a formula to both another worksheet and to another workbook.

Question 9

A delimiter is a data separator.

Question 10

You cannot use the fill handle to copy a formula with an external reference indicator.

Question 11

The parts of an IF function are separated by parentheses.

Question 12

The IFERROR function is used to look for spelling errors in Excel.

Question 13

Linking to a workbook means that any changes to the original values are automatically reflected in the consolidation sheet as well.

Question 14

If a number is divided by zero,the error value #DIV / 0! will display in the cell.

Question 15

Data can be consolidated from different workbooks in one worksheet.

Question 16

The AND function returns a value of TRUE if every logical test in the formula is true.

Question 17

One way to check worksheet formulas for errors is to display the formulas on the worksheet rather then the formula results.

Question 18

If there are invalid cell references in a formula,the error value #REF! will display in the cell.

Question 19

The CONCATENATE function breaks a text string into separate strings.

Question 20

Cell and range names can use uppercase and lowercase letters,digits,and spaces.

Question 21

Which Excel function averages cells in a range based on a specified condition?

A) AVERAGEIF
B) AVERAGE
C) COUNTIF
D) SUMIF

Question 22

The ____ refers to where you will be able to use a named cell or range in worksheets.

A) range
B) location
C) scope
D) definition

Question 23

Which of the following is not a valid named range?

A) Alaska59
B) New quote
C) salary
D) Top_Sales

Question 24

Which error code will display if a number is used invalidly in a cell?

A) #NUM!
B) #VAL
C) #VALUE!
D) #NAME?

Question 25

Names of cells and ranges cannot use ____.

A) uppercase letters
B) lowercase letters
C) digits
D) spaces

Question 26

The CONCATENATE function joins two or more ____ into one.

A) strings
B) formulas
C) cells
D) functions

Question 27

Which error code will display if a column is not wide enough to display data?

A) #####
B) #NUM!
C) #NULL!
D) #NA

Question 28

Which error code will display if a value in a formula is not available?

A) #NAME?
B) #VAL
C) #NONE
D) #NA

Question 29

Which Excel function counts the number of cells in a range?

A) SUMIF
B) AVERAGEIF
C) COUNTIF
D) TOTALSUM

Question 30

When used in formulas,names become ____ cell references by default.

A) absolute
B) relative
C) linked
D) unlinked

Question 31

Cell references that reference other sheets behind the summary sheet are known as ____ references.

A) 3-D
B) Mirror
C) Backstage
D) Stage

Question 32

Which Excel feature is used to break a data field into separate columns?

A) Break
B) Separate Columns
C) Convert Text to Columns
D) Concatenate

Question 33

Which Excel function totals only the cells in a range that meet given criteria?

A) COUNTIF
B) CONCATENATE
C) SUMIF
D) TOTALNUM

Question 34

Which button do you click in the Function Library group to select SUMIF from the list of functions?

A) Statistical
B) Math & Trig
C) Math Functions
D) Trig & Calculus

Question 35

Which Excel function checks for errors and displays a message you specify if there is an error in a formula.

A) ERROR
B) #ERROR
C) IFERROR
D) #IFERROR

Question 36

Which text function capitalizes the first letter in a string of text?

A) UPPER
B) CAPITAL
C) FIRST
D) PROPER

Question 37

One way to display formulas is by pressing this key combination.

A) [CTRL][+]
B) [CTRL][`]
C) [CTRL][F]
D) [CTRL][*]

Question 38

What Excel features uses worksheet data to predict what should be entered into similar column cells?

A) Auto Complete
B) Flash Fill
C) Quick Fill
D) Auto Fill

Question 39

Which error code will display if a formula contains a text error?

A) #NA
B) #NUM!
C) #NAME?
D) #####

Question 40

In the accompanying figure,which numbered item identifies the logical test?

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

Question 41

In the accompanying figure,what is the name of the function in the formula of the active cell?

A) Monthly Payment
B) D6
C) -B6
D) PMT

Question 42

In the accompanying figure,which of the three banks offers the lowest interest rate?

A) Commercial Bank
B) Venture Credit Union
C) First National Bank
D) All three offer the same interest rate.

Question 43

In the accompanying figure,in the formula in the active cell,why is B6 entered as -B6?

A) To reflect a calculation for a reverse mortgage.
B) To show that borrowing money is a negative activity.
C) To show the monthly payment as a positive number.
D) To subtract the amount of the loan from the total payments.

Question 44

In the accompanying figure,there are ____ arguments in the formula.

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

Question 45

Which of the following is NOT a comparison operator you can use in Excel?

A) <
B) >
C) =
D) $

Question 46

In the accompanying figure,item 2 refers to the ____.

A) cell name
B) range name
C) worksheet name
D) workbook name

Question 47

Which of the following is NOT one of the arguments of a PMT function?

A) payment
B) pv
C) nper
D) rate

Question 48

The ____________________ Text to Columns feature breaks data fields in one column into separate columns.

Question 49

Which comparison operator means not equal to?

A) <>
B) #=#
C) ##
D) !!

Question 50

The ____________________ function joins two or more text strings into one.

Question 51

A cell with a(n)____________________ reference contains a formula that refers to its own cell location.

Question 52

A condition that can be answered with a true or false response is called a(n)____.

A) logical test.
B) what-if analysis.
C) True_False condition
D) IF analysis

Question 53

You can reference,or ____________________ to,data in other sheets and in other workbooks.

Question 54

After you name a cell or range,you can define its ____________________,or the worksheets where you will be able to use it.

Question 55

In the accompanying figure,item 1 refers to the ____.

A) cell reference
B) worksheet name
C) function name
D) workbook name

Question 56

The Name ____________________ dialog box can be used to delete and edit worksheet names.

Question 57

In the accompanying figure,____ separates the condition from the action to be taken.

A) ,
B) >
C) *
D) =

Question 58

In the accompanying figure,in the formula in the active cell,why is cell D6 divided by 12?

A) To convert the annual interest rate to a monthly interest rate.
B) To convert the number of payments to a monthly value.
C) To convert the amount borrowed to a monthly value.
D) To determine how much interest is paid each month.

Question 59

In the accompanying figure,how many arguments does the formula in the active cell use in the calculation?

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

Question 60

A(n)____________________ is a data separator; Excel uses it to separate data into columns.

Question 61

In the accompanying figure,item number ____________________ indicates the action that Excel should take if the test is false.

Question 62

You should use the ____________________ function to reverse the value of an argument.

Question 63

The ____________________ function contains three parts: a condition or logical test,an action to take if the condition is true,and an action to take if the condition is false.

Question 64

The ____________________ function calculates the periodic payment amount for money borrowed.

Question 65

The ____________________ function returns TRUE if any of its arguments are true.

Question 66

In the accompanying figure,item number ____________________ indicates the action that Excel should take if the test is true.

Question 67

The less than (<)and more than (>)symbols are examples of ____________________ operators.

Question 68

Describe two examples of why you would need to use the PMT function.

Question 69

In the accompanying figure,item number ____________________ indicates the function name.

Question 70

The ____________________ function calculates the future value of an investment.

Question 71

The comparison operator > means ____________________ than.

Question 72

When the condition is a question that can be answered with a true or false response,Excel calls this stated condition a(n)____________________ test.

Question 73

Explain how the SUMIF function works.Use an example.