Question 1

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.

Accepted Answer

B,C,D

Question 2

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.

Accepted Answer

The issue likely arises from the formula not correctly referencing the intended cells after being copied. Using an absolute cell reference (indicated by $ in the cell address, like $A$1) ensures the reference remains constant, regardless of where the formula is copied. This prevents the formula from adjusting to new rows and potentially referencing empty cells or cells with zero values, which would result in zeros in the copied cells.

Question 3

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)

Accepted Answer

The correct formula to round a number in a cell to a specified number of decimal places in Excel is =ROUND(cell, number_of_places). In this case, to round the number in cell A2 to two decimal places, the correct formula is =ROUND(A2,2). The reference to A5 in the options is incorrect and seems to be a typo; it should refer to A2 based on the question's context. However, considering the options provided and the intent to round to two decimal places, option A is the correct approach despite the cell reference error. MROUND rounds to the nearest multiple of a specified number, which doesn't directly apply to rounding to a specific number of decimal places as intended here.

Question 4

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

Accepted Answer

$B$2 is an absolute cell reference because the dollar signs ($) are used to lock the column and row references, thereby preventing them from changing when the formula is copied to other cells.

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

Accepted Answer

The correct formula to convert a date in text format to its serial number is by using the DATEVALUE function with the date enclosed in double quotes. Therefore, =DATEVALUE("01/01/2017") is the correct syntax, which corresponds to option D.

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

Accepted Answer

By using $A$5, we are using absolute reference, which means that the reference to cell A5 will not change when the formula is copied to other cells. This is important in this case as many formulas refer to that price and we do not want the reference to shift when copied.

Question 7

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

Accepted Answer

The MIN function is used to find the lowest value in a range of values, therefore it is the best choice for entering the lowest price from a range of prices in Excel.

Question 8

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

Accepted Answer

This is true as the DATE function returns the selected year, month, and day as a date in Excel.

Question 9

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

Accepted Answer

To preserve an exact cell address in a copied formula, you need to use an absolute cell reference by adding a dollar sign ($) in front of both the column and row reference ($A$1). A relative cell reference will change automatically when the formula is copied to another cell.

Question 10

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

Accepted Answer

The Insert Function dialog box provides a list of available functions, their categories, and brief descriptions of each function.

Question 11

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)

Accepted Answer

The correct formula to insert the date January 12, 1998 into a cell is =DATE(1998,1,12). The format for the DATE function is =DATE(year, month, day) where year is represented by four digits, month is represented by a number from 1 to 12, and day is represented by a number from 1 to 31.

Question 12

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

Accepted Answer

The IF function checks if B2 is greater than or equal to 60, and if it is, the result is "Pass." If it is not, the result is "Fail." This accurately checks whether the student has passed or failed based on their score in B2. Option B is incorrect because the OR function does not work with text ("Pass" and "Fail" in this case), and it also does not make sense to use OR in this situation since we are only checking one condition (B2>=60). Option C is incorrect because the AND function also does not work with text, and the syntax for the AND function is incorrect (it requires multiple Boolean conditions, not a condition and text values). Option D is incorrect because the NOT function is unnecessary and the syntax is again incorrect (it is trying to use the OR function with a condition and text values).

Question 13

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

Accepted Answer

The correct function is using nested IF statements with the logical operators "<" to check if the score falls within a certain range. A score from 0 to 59 should return an "F", a score from 60 to 69 should return a "D", a score from 70 to 79 should return a "C", a score from 80 to 89 should return a "B", and a score from 90 to 100 should return an "A". Choice A correctly follows this logic and uses the correct symbols for the logical operators. The other choices have errors in their syntax, such as using "

Question 14

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

Accepted Answer

The formula =DAY(A1) will extract the day from the date in cell A1. Option B is incorrect because it contains an extra set of quotation marks. Option C is incorrect because it tries to use the DAY function inside the DATE function, which is not necessary since the date is already in cell A1. Option D is incorrect because it uses quotation marks around "DAY", which is not a valid function.

Question 15

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

Accepted Answer

By typing NOW() in cell B1, the current date and time will be inserted. To insert only the current date, use the formula =TODAY() in cell B1.

Question 16

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

Accepted Answer

The Fill handle, indicated by a small square at the bottom right corner of the selected cell, can be dragged to adjacent cells to copy the formula.

Question 17

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

Accepted Answer

The function AVERAGE is specifically designed to calculate the arithmetic mean of a list of values. MAX returns the highest value in a range, COUNT returns the number of cells in a range, and SUM returns the sum of a range of values, but none are specifically designed to calculate the arithmetic mean. Therefore, the best choice is D, AVERAGE.

Question 18

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

Accepted Answer

The "X" in the procedure refers to "What-if Analysis," which is the correct option for accessing Goal Seek in Excel. Goal Seek is a part of the What-if Analysis tools that allow users to find the input value needed to achieve a desired result.

Question 19

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.

Accepted Answer

This is the correct procedure for inserting an equation in Microsoft Word.

Question 20

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(...)

Accepted Answer

The function XOR (exclusive or) returns TRUE if only one of the arguments is true, and FALSE if both are true or both are false. In this case, if either of the statements is true, XOR will return TRUE, which is what Nathan wants to elicit.

Quiz 3: Performing Calculations With Formulas and Functions

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

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

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

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?

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?

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

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

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

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

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

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?

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?

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

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

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

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

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?

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.

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?