Categories

# Formulas and Functions in Excel 2013

## Objective Type Questions

### Fill in the blanks:

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.

### State whether True or False:

1. By default, Excel creates a relative cell reference in a formula. (TRUE)
2.  The Cancel button present near the formula bar works as the Enter key. (FALSE) (Works as Escape key)
3. The error ‘#####’ appears if the formula contains an invalid operation. (FALSE) (When the column is not wide enough to display the value)
4. All cells in rows 5 through 9 are referred as 5:9. (TRUE)
5. In the order of evaluation, the addition operator (+) has higher precedence than the exponent operator (^). (FALSE) (Exponent has higher precedence)

### 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. =A1*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. non eof 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 referencing is relative, the column reference will be ________.
a. relative
b. absolute
c. either relative or absolute
d. none of these

## Descriptive Type Questions

### 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 cell 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 cell 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?
Joining strings is called concatenation. We use the ampersand (&) character to concatenate strings.
4. When would you get the error ‘#####’ in Excel?
We get ‘#####’ error in Excel when the column is not wide enough to display the value.
5. Explain how to use the Insert Function feature in Excel 2013.
The Insert Function command helps us to insert a formula by selecting the required function and the cells we want to reference, in the relevant dialog boxes.
Following are the steps:
a. We click the cell in which we want to enter a formula.
b. Click the Insert Function option on the Formula bar.
c. In the Insert Function dialog box, select All in the Or select a category.
d. In the Select a function box, we select the required function and then click OK.
e. In the Function Argument dialog box, we enter the values or cell references, or select the cells that we want to reference.
f. We click OK on the dialog box.

## Extra Questions

### Fill in the blanks:

1. An Excel formula always starts with an equal to (=) sign.
2. When you are not doing anything, the status remains Ready.
3. When you are entering data in a cell, the status changes to Enter.
4. When you double-click or press F2 to edit a cell, the status changes to Edit.
5. If circular references occur, the status bar displays CIRCULAR REFERENCES: followed by the address of one cell having circular reference.
6. A text string or text value is a sequence of characters on which you cannot perform mathematical operations.
7. There are 1048576 rows and 16384 columns in a worksheet in Excel 2013.
8. In Excel 2013, a worksheet has 17179869184 cells.
9. #DIV/0! error occurs when the formula contains an invalid operation, i.e. division by zero.
10. #N/A error occurs when the formula uses a value that is not available.
11. #NUM! error occurs when there is a problem with a value.
12. #REF! error occurs when the formula refers to a cell that does not exist.
13. #VALUE! error occurs when the formula has an invalid argument.
14. The AutoSum button automatically adds numbers in the selected cells.
15. The AutoSum button is present in the Editing group on the HOME tab.
16. The Quick Analysis tools allow you to perform calculations on the numbers quickly.
17. The Function Library group shows all the functions present in Excel, arranged in various categories.
18. The GCD function calculates the greatest common divisor of two or more integers.
19. GCD is also known as highest common factor.
20. The LCM function calculates the least common multiple of two or more integers.
21. Click the New Sheet button located at the right edge of the worksheet tab bar to add a new worksheet.

### Answer the following:

1. What is a formula in Excel?
A formula in Excel is a sequence of values, cell references, functions, and/or operators stored in a cell, that produces a new value from existing values.
2. What is a Range in Excel?
A rectangular block of contiguous cells is called a range.
3. What is an operator?
An operator in Excel is a symbol that tells Excel to perform a specific operation.
4. Name the main mathematical operators in Excel.
Following are the main mathematical operators in Excel:
a. Exponent (^)
b. Multiplication (*)
c. Division (/)
d. Addition (+)
e. Subtraction (-)
5. What do you mean by pointing in Excel?
An easier method of entering a cell reference is to click on the cell instead of typing the cell address. This method is called pointing.
6. What is circular cell reference?
Circular cell reference occurs when a formula refers to its own value, either directly or indirectly.
7. What is a function in Excel?
Functions in Excel are predefined formulas that perform specific calculations. Examples: MAX, MIN, COUNT, etc.
8. Define greatest common divisor.
The greatest common divisor is the largest integer that can divide the numbers without a remainder.
9. Define least common multiple.
The least common multiple is the smallest positive integer that is a multiple of all the given numbers.
10. How do we rename a worksheet?
We right-click on the worksheet name and select the Rename command and enter a new name. Or, we can double-click the worksheet name and enter a new name.
11. How do we delete a worksheet?
We right-click the worksheet name and select Delete from the shortcut menu.

### State whether True or False:

1. Operations are carried out in the order in which they occur in the formula, which is left to right. (TRUE)
2. Any operation contained in brackets will be carried out first, followed by any exponent (power). (TRUE)
3. If two operators have the same precedence, Excel simply evaluates them from left to right. (TRUE)
4. The type of cell reference becomes important only when you have to copy a formula to other cells. (TRUE)
5. Excel 2013 contains hundreds of functions. (TRUE)
6. The AutoSum button can also be used for finding the average, maximum, minimum, and count of values for the selected cells. (TRUE)
7. A new Excel workbook contains only one worksheet named as Sheet1 by default. (TRUE)

### Shortcut Keys:

1. Fill Down command: Ctrl + D
2. Fill Right command: Ctrl + R
3. To have Excel cycle through all the four reference types: F4

## 9 replies on “Formulas and Functions in Excel 2013”  Spandan Banerjeesays:

Nice and very helpful  sayan ranasays:

WAP in Java to accept a number and check whether the number is Achilles number or not.

Achilles number: In Mathematics, an Achilles number is a number that is powerful ( A number n is said to be Powerful Number if for every prime factor p of it, p^2 also divides it ) but not a perfect power .(A perfect power is a number that can be expressed as power of another positive integer. Eg 1,4,8,9…)

The first few Achilles number are-

72, 108, 200, 288, 392, 432, 500, 648, 675, 800, 864, 968, 972, 1125, 1152, 1323  adminsays:

https://www.happycompiler.com/achilles/  sayan ranasays:

WAP in Java to accept a number and check whether the number is Achilles number or not.

Achilles number: In Mathematics, an Achilles number is a number that is powerful ( A number n is said to be Powerful Number if for every prime factor p of it, p^2 also divides it ) but not a perfect power.

The first few Achilles number are-

72, 108, 200, 288, 392, 432, 500, 648, 675, 800, 864, 968, 972, 1125, 1152, 1323  Devrajsays:

Very nice and helpful  adminsays:

Thank you!  Saikatsays:

Thanks very much

Thanks  Siddhartha Banerjeesays:

Thanks! for this wonderful website for education of computers

This site uses Akismet to reduce spam. Learn how your comment data is processed.