Deck 19: Using Powerful Excel Functions

Question 1

The ____ function locates data when it is displayed vertically in the lookup table.

A) FINDV
B) COUNTV
C) VLOOKUP
D) VERTLOOKUP

Answer 1

C

Question 2

The ____ function is used to total only cells that meet a certain condition.

A) SUMIF
B) COUNTIF
C) COUNTA
D) SUMWHEN

Answer 2

A

Question 3

The COUNTA function counts the number of cells that do not have any numbers or text entered in them.

Answer 3

False

Question 4

When the Subtotal function is used by clicking the Subtotal button on the Data tab, data is summed within the data range to give subtotals and a grand total.

Question 5

After data is filtered, only the cells for the item you filtered will be displayed; the other cells will be hidden.

Question 6

The function number 5 calculates SUM.

Question 7

You can count cells that have text with the COUNT function.

Question 8

Table_array is the range of cells for a lookup table.

Question 9

Excel does not accept spaces in named ranges.

Question 10

The SUMIF function will only total data that meets a certain condition.

Question 11

To use the HLOOKUP function, you will need the row number from the table that has the data you want returned to the cell with the HLOOKUP function.

Question 12

The VLOOKUP function finds data when it is displayed horizontally in the lookup table.

Question 13

To count the number of cells without content, you will use the ____ function.

A) EMPTY
B) COUNTE
C) COUNTA
D) COUNTBLANK

Question 14

To count cells with text, you use the ____ function.

A) COUNTA
B) COUNT
C) SUMIF
D) VLOOKUP

Question 15

You can count numerical data in a range using the ____ function.

A) COUNTA
B) SUMIF
C) LOOKUP
D) COUNT

Question 16

After you name a range, Excel remembers that name.

Question 17

Named ranges are helpful if you need to use a range in several functions.

Question 18

Using the COUNT function lets you count only cells that are displayed.

Question 19

You use the HLOOKUP function when the lookup table is listed vertically in columns.

Question 20

For the Row_index_num of the HLOOKUP function, you will need to specify what to look for in the lookup table.

Question 21

The Lookup & Reference button is found on the ____ tab.

A) Home
B) Insert
C) Formulas
D) Data

Question 22

If you want to use a name range with two words, you will need to use a(n) ____ to separate the words.

A) comma (,)
B) space
C) underscore (_)
D) period (.)

Question 23

Case EX 13-1 Kim is a photographer. To supplement her income, she also teaches a basic photography class in her studio. She uses an Excel workbook to keep track of information related to her class. The columns in her worksheet are: Student Name, Address, Phone number, Fees, Fees Paid. When Kim was reviewing her worksheet, she realized that some of the addresses are missing. To find out the number of students for whom she doesn't have an address, she needs to use the ____ function.

A) COUNT
B) COUNTBLANK
C) COUNT
D) EMPTY

Question 24

When you use the VLOOKUP function, you will need to enter information into the ____ dialog box.

A) Functions
B) Function Arguments
C) Lookup
D) Lookup Data

Question 25

To find the total number of cells that have numeric data, you use the ____________________ function.

Question 26

Figure EX 13-1 In Figure EX 13-1 above, item 1 points to the ____.

A) Formula Bar
B) Row Box
C) Legend
D) Name Box

Question 27

As you are typing a range name into a formula, ____.

A) the cells become bold
B) the cells become hidden
C) a border appears around the cells
D) the background color of the cells changes

Question 28

You can use the ____ function to get the total amount for data in a range of cells.

A) DATA
B) COUNTBLANK
C) SUM
D) TOTALD

Question 29

The function number ____ calculates AVERAGE.

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

Question 30

You select a named range by clicking the ____ down arrow and then selecting the named range.

A) Range
B) Formula Bar
C) Select
D) Name Box

Question 31

The ____ function can count cells if they contain text or a combination of text and numbers.

A) COUNT
B) SUM
C) SUMIF
D) COUNTA

Question 32

Figure EX 13-1 In Figure EX 13-1 above, item 2 points to Amount, which is the ____.

A) function text
B) named range
C) condition
D) row name

Question 33

Case EX 13-2 Gregory works for the local public television station. The station has recently held a TV pledge drive and they have received a large number of pledges from viewers and businesses. The data about the pledge drive is stored in a worksheet. The worksheet has six columns: First Name, Last Name, Address, Phone, Pledge, Received. The Received column contains "Yes" or "No" values depending on whether the amount pledged has been received by the station. From only the pledges which have been received, Gregory wants to find out the average amount pledged. He can first filter the data and then use the ____ function.

A) SUMIF
B) COUNTA
C) COUNT
D) Subtotal

Question 34

The Subtotal function is ____.

A) =SUBTOTAL (function number, data range)
B) =SUBTOTAL (data range, function number)
C) =SUBTOTAL (data range, function number, result range)
D) =SUBTOTAL (function number, data range, result range)

Question 35

Case EX 13-1 Kim is a photographer. To supplement her income, she also teaches a basic photography class in her studio. She uses an Excel workbook to keep track of information related to her class. The columns in her worksheet are: Student Name, Address, Phone number, Fees, Fees Paid. To find out the number of students in her class based on the Student Name column, she can use the ____ function.

A) COUNT
B) COUNTA
C) SUM
D) SUMIF

Question 36

Case EX 13-2 Gregory works for the local public television station. The station has recently held a TV pledge drive and they have received a large number of pledges from viewers and businesses. The data about the pledge drive is stored in a worksheet. The worksheet has six columns: First Name, Last Name, Address, Phone, Pledge, Received. The Received column contains "Yes" or "No" values depending on whether the amount pledged has been received by the station. If Gregory wants to find out the total amount of money that has actually been received by the station, he needs to use the ____ function.

A) COUNT
B) SUMIF
C) COUNTA
D) VLOOKUP

Question 37

Use the ____ function when the data in the lookup table is listed horizontally in rows.

A) HLOOKUPTABLE
B) VLOOKUPTABLE
C) HLOOKUP
D) VLOOKUP

Question 38

You will use the ____ function to see how many cells do not contain data.

A) EMPTY
B) COUNT
C) BLANK
D) COUNTBLANK

Question 39

____ functions allow you to find data that already exists rather than looking for the data yourself.

A) FIND
B) SUM
C) LOOKUP
D) Subtotal

Question 40

The ____ function can be used to count, sum, or average filtered data.

A) Calc
B) Subtotal
C) SUM
D) COUNT

Question 42

Explain why you might need to use the Subtotal function, instead of the COUNT function, when dealing with filtered data.

Question 43

The VLOOKUP function is found on the ____________________ list.

Question 44

The SUMIF function is another form of the ____________________ function.

Question 45

To use the HLOOKUP function, you will need to enter information into the ____________________ dialog box.

Question 46

The ____________________ lets you control the calculation that the Subtotal function performs.