Formulas and Functions in Excel 2013

Formulas and Functions in Excel 2013

Spreadsheets: Functions

Fill in the blanks with the correct words.

1. The ✓ button on the Formula bar works as Enter key.

2. $B7 is an example of mixed referencing.

3. Joining two strings is called concatenation.

4. Formulas can be entered on the Formula bar or in the cell.

5. The multiplication (*) and division (/) arithmetic operators have the same order of evaluation.

Write T for the true statement and F for the false one. Correct the false statement(s).

1. By default, Excel creates a relative cell reference in a formula. (T)

2. The Cancel button present near the Formula bar works as the Enter key. (F) (The Cancel button present near the Formula bar works as the Esc key)

3. The error ‘#####’ appears if the formula contains an invalid operation. (F) (The error ‘#####’ appears if the column is not wide enough to display the value)

4. All cells in rows 5 through 9 are referred as 5:9. (T)

5. In the order of evaluation, the addition operator (+) has higher precedence than the exponent operator (^). (F) (The addition operator (+) has lower precedence than the exponent operator (^))

Choose the correct option.

1. If cell B1 has the formula = A1 * $C$1 and the formula is copied to cell B2, then which of these will be the formula in B2?

a. = A2 * $C$1 b. = A1 * $C1 c. = A! * C$1 d. None of these

2. In absolute referencing, the row and column references ________ when you copy a formula.

a. change b. do not change c. sometimes change d. none of these

3. Which of these functions is used to add the values of several cells?

a. AVERAGE b. MAX c. MIN d. SUM

4. If you copy the formula = $B7 to another cell, which of these options will you observe in the result?

a. The row part remains the same.

b. The column part remains the same.

c. Both row and column parts remain the same.

d. Both row and column parts change.

5. In mixed referencing, if the row reference is relative, the column reference will be ________.

a. relative b. absolute c. either relative or absolute d. none of these

Answer the following.

1. What is the difference between the three types of cell referencing?

In relative cell referencing, the row and column references can change when we copy the formula to another cell. It is the default cell referencing.

In absolute referencing, the row and column references do not change when we copy a formula because the reference points to a fixed cell address. The $ sign is used for absolute referencing.

In mixed referencing, if the row reference is relative, the column reference will be absolute and vice versa.

2. How will you refer to the cell in column B and row 2?

We will refer to the cell in column B and row 2 as B2.

3. What do you mean by concatenation?

Concatenation is the process of joining text values.

4. When would you get the error ‘#####’ in Excel?

The ‘#####’ error indicates that the column is not wide enough to display the value.

5. Explain how to use the Insert Function in Excel 2013.

Following are the steps to use the Insert Function in Excel 2013:

a) We click on the cell in which we want to enter a formula.

b) Then we click the Insert Function option on the Formula bar.

c) Then we select the required function from the dialog box that appears.

d) We then also enter the required values that the function requires.

e) Finally, we click OK.