Excel Module 3

If the VLOOKUP function cannot find a match in the lookup table, what does it return?

#N/A error value

Your worksheet contains a price in cell A5, and many formulas refer to that price. How would you refer to that price in the formulas? A. A5

$A$5

Which formula can be applied for the given data in a cell A2: 01/01/2017 to get the serial number of the date as text?

. =DATEVALUE(''01/01/2017'')

What is the correct formula to insert the date January 12, 1998 into a cell?

=DATE(1998,1,12)

Sheila wants to extract the hour from the time, 12:00:56, in cell A1. Which formula can she use to do this?

=HOUR(A1)

The score of a student is inserted in cell B2. The passing score for the subject is 60. Which of the following functions should you insert in cell C2 to check whether the student has passed or failed?

=IF(B2 >= 60, "Pass", "Fail")

Which of the following formulas can you use to extract the minute from the time in cell G5: 10:00:56?

=MINUTE(G5)

Which formula can you use to extract the month number from the date entered in cell F5 as July 8, 2016 ?

=MONTH(DAY(2016,7,0))

Cell A5 contains the number 24.7835. In another cell, Ariane wants to enter the number from cell A5 but rounded to two decimal places. Which of these formulas can she use to do so?

=ROUND(A5,2)

Jason wants to average the sales in the range C3:C50, and then round the result to the nearest integer. Which of the following formulas should he use?

=ROUND(AVERAGE(C3:C50),0)

In the formula =B6*$B$2, which of the following describes $B$2?

Absolute Cell Reference

Which of the following lets you extend any data pattern involving dates, times, numbers, or text?

AutoFill Options button

Which of the following functions would you use to calculate the arithmetic mean of a price list?

Average

When Valerie used AutoFill to copy a formula, Excel copied the format and removed shading from some cells. What can she do to prevent the formatting change?

Click the AutoFill Options button and then click Fill Without Formatting.

You have selected a cell with a formula. Which of the following can you use to copy that formula to an adjacent cell?

Fill handle (Command)

Nathan wants a formula to return "Yes" if the value in cell A1 is less than the value in cell B1, and to return "No" otherwise. Which of the following functions should he use?

IF

Which of the following is a type of logical function?

IF

You've copied a cell containing formula to the rows below it, and the results in the copied cells are all zeros. To find the problem, what should you check for in your original formula?

If it needs an absolute cell reference

Which of the following Excel tools displays the syntax for a selected function?

Insert Function Dialog Box

In column D of a worksheet, Cal lists the number of hours each employee works. What function should he use to find the employee who worked the most hours?

MAX

To have Excel enter the lowest price from a range of prices, which of the following would you use?

MIN function

Kathy wants to add the numbers entered in a range of selected cells. To do this, which tab will she click on the menu that appears after clicking on Totals in the Quick Analysis option?

SUM

Arlo wants to use Goal Seek to answer a what-if question in a worksheet. Which button should he click on the Data tab to access the Goal Seek command?

What-if-Analysis

What does the third argument (3) refer to in the following formula: VLOOKUP(10005, A1:C6, 3, FALSE)

number of the column containing the return value