Deck 3: Performing Calculations With Formulas and Functions

Question 1

To preserve an exact cell address in a copied formula, you can use a relative cell reference.

Answer 1

False

Question 2

Which of the following is true about entering a function using the Insert Function dialog box?

A) You open the dialog box by typing "function" in the formula bar.
B) You can search for a function that meets your needs.
C) You don't need to type an equal sign to begin the formula.
D) You can select a function you've recently used.

Answer 2

B, C, D

Question 3

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?

A) If it needs an absolute cell reference.
B) If it needs a relative cell reference.
C) If it needs landscape orientation.
D) If it needs a function.

Answer 3

A

Question 4

Where can you see a brief description of a selected function?

A) Formula bar
B) Insert Function dialog box
C) Insert dialog box
D) Insert tab

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

A) =DATEVALUE(1-Jan-2017)
B) =DATEVALUE(01/01/2017)
C) =DATEVALUE(1st Jan, 2017)
D) =DATEVALUE(''01/01/2017'')

Question 6

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
B) $A$5
C) $A5
D) A$5

Question 7

Jose inserts the formula '=DATE('2000, 2, 2)' in cell AI5. Upon pressing ENTER, 2/2/2000 will appear in AI5.

Question 8

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

A) MAX
B) COUNT
C) SUM
D) AVERAGE

Question 9

A student wants to round the number 24.7835 entered in cell A2 to two decimal places. Which of these formulas can she use to do so?

A) =ROUND(A5,2)
B) =MROUND(A5,2)
C) =ROUND(A5,2.0)
D) =MROUND(A5,0.2)

Question 10

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

A) MAX function
B) COUNT function
C) MIN function
D) COUNTA function

Question 11

To insert an equation, click on the Object in the Text group on the Insert tab, then click Microsoft Equation 3.0 in the Object type box, and then click OK.

Question 12

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

A) Relative cell reference
B) Absolute cell reference
C) Function
D) Average

Question 13

To find the largest value in a cell range, use the MIN function.

Question 14

The score of five students in a subject are inserted in row B from B2 to B6 cells and the grades for the scores are: A: 90 to 100, B: 80 to 89, C: 70 to 79, D: 60 to 69, and F: 0 to 59. Which of the following functions can you insert in the D2 cell so that it returns the grade for each score in row B?

A) =IF(B2<60,"F",IF(B2<70,"D",IF(B2<80,"C",IF(B2<90,"B","A"))))
B) =IF(B2<59,"F",IF(B2<69,"D",IF(B2<79,"C",IF(B2<89,"B","A"))))
C) =IF(B2D) =IF(B2=0-59,"F",IF(B2=60-69,"D",IF(B2=70-79,"C",IF(B2=80-89,"B",IF(B2=90-100,"A"))))

Question 15

Arlo wants to use goal seek to answer "what if" questions in a file. To do this, he clicks the cell in the spreadsheet that he wants to change, then selects the "X" button from the Data tab and selects Goal seek... from the drop down menu. What is X in the procedure?

A) What-if Analysis
B) Data Validation
C) Consolidate
D) Relationships

Question 16

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

A) =DATE(1998,1,12)
B) =DATE(1998,12,1)
C) =DATE(12-1-1998)
D) =DATE(1/12/1998)

Question 17

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

A) mode indicator
B) Page Break Preview
C) scroll bar
D) Fill handle

Question 18

Nathan wants TRUE to be elicited if either of the statements are true, and FALSE if both are true. Which of the following formula can he use to ensure that?

A) =IFNA(...)
B) =XOR(...)
C) =IFERROR(...)
D) =TRUE OR FALSE(...)

Question 19

To insert the current date in cell B1, type CURRENT DATE in cell A1 and NOW() in cell B1 and press ENTER.

Question 20

The score of students in a subject is inserted in B2 cell and the passing score for the subject is 60. Which of these functions will you insert in C2 cell to check if the student has passed or failed?

A) =IF(B2>=60, "Pass", "Fail")
B) =OR(B2>=60, "Pass", "Fail")
C) =AND(B2>=60, "Pass", "Fail")
D) =NOT(OR((B2>=60, "Pass", "Fail"))

Question 21

Which of the following lets you fill a cell with only specific elements of a copied cell?

A) relative cell reference
B) Auto Fill Options button
C) argument
D) mode indicator
E) SHEET

Question 22

In which of the following boxes under Define target value will you type a numerical value while creating a key performance indicator (KPI)?

A) Absolute value
B) Select icon style
C) Define target value
D) Define status thresholds

Question 23

A student types the formula '= YEAR( TODAY())-2005' to calculate the age of a person born in 2005. This formula will use the TODAY function as an argument for the YEAR function to obtain the current year, and then subtract 2005 returning the person's age.

Question 24

Which formula can you use to extract the month from the date in cell F5: 2016 July 8th?

A) =MONTH(F5)
B) =MONTH("F5")
C) =MONTH(DATE(2016,7,0))
D) =MONTH(DAY(2016,7,0))

Question 25

The formula =ROUNDDOWN(G5,0) can be used to round the number 46.734, entered in cell G5, to the nearest whole number.

Question 26

Stuart inserts the formula '=YEAR ('23-Aug-2012)' into cell B12. When he presses ENTER, 23/08/2012 will appear in B12.

Question 27

A student inserts the formula =TIMEVALUE("10:30 AM") in a cell. On pressing ENTER, it will return the value 10:30.

Question 28

The formula =ROUNDUP(G5,0) can be used to round up 59.425, entered in cell G5, to 0.

Question 29

Ian needs to extract the day from the date 5/31/2017 entered in cell A1. Which formula can he use to do this?

A) =DAY(A1)
B) =DAY("A1'')
C) =DATE(DAY(A1))
D) =DATE("DAY"(A1))

Question 30

A student is asked to find the difference between two values of time entered in cells A1 and B1, where A1 is the greater value and B1 the lesser value. In order to do so, he will type the formula =A1-B1 in an empty cell.

Question 31

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

A) MINUTE(G5)
B) =MINUTE(G5)
C) =MINUTE("10:00:56")
D) MINUTE("10:00:56")

Question 32

A jewelry designer creates an excel sheet to list different items he sold on the previous day. Cells A1, B1, C1, and D1 contain headings-Order ID, Unit price, and Quantity, respectively. Cells A2 to A6 list the order ID for items sold-10001,10002,10003,10004, and 10005, respectively. Cells B2 to B6 list their respective unit price-$15, $18.5, $12, $9.5, and $6. Cells C2 to C6 list their respective quantities-20, 32, 16, 15, and 10. If he enters the formula in cell D1 as: =VLOOKUP (10005, A1: H3, 3, FALSE), what will the formula return?

A) 10
B) $6
C) $12
D) 10003

Question 33

To round time 10:30 pm inserted in cell A4 to the nearest 30-minute interval, the formula: =MROUND(A4,0:30) can be used.

Question 34

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?

A) Sum
B) Count
C) Average
D) %Total

Question 35

Rohan wants to create a column containing dates and want to display days beside each date in the next column. He can use the formula '=CHOOSE(WEEKDAY(B5),"Mon", "Tue", "Wed", "Thur", "Fri", "Sat")'.

Question 36

Kamala enters a date as the start date in cell E2 and a list of holidays in cells F2:F5. Now she wants to add 30 workdays to the start date. She can do this using the following formula: = WORKDAY (E2, 30, F2:F5).

Question 37

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

A) HOUR(A1)
B) =HOUR(A1)
C) =HOUR("12:00:56")
D) HOUR("12:00:56")

Question 38

To extract the seconds from the time entered in cell B1 as 12:00:52 PM, type =SECOND(B1) in cell C1 and press ENTER.