Solution of chapter 4: Excel Formula and function

 Chapter: 4 - Excel Function and Formulas

This chapter is the continuation of the chapter 3, with additional features to learn and explore. Learning the content of this chapter will help you to create and format worksheet by applying formula and function for carrying our calculation for various needs. Also this chapter has given basic introduction to the components of charts or graph that are also very important tools for excel to represent data in perfectly in excel.

Here the solution for the chapter 4 has been given for all of you to check the answer of the solution we have discussed during the class.
A. Tick the correct answer:
1. ______ is a sequence of values, cell reference and operator to do calculation.
    a. reference     b. Formula (Correct answer)     c. Value
2. A range name must start with a _____.
    a. Number     b. Letter(Correct answer)     c. Symbol
3.  _________ are specific values used in particular order to perform calculation in functions.
    a. Arguments(Correct Answer)     b. Numbers     c. Digits
4. ________ is the central area within the chart where actual chart is plotted.
    a. Legend     b. Data Series     c. Plot Area(Correct Answer)
5. Text at the top of the chart is called________.
    a. Axis Title     b. Chart Title(Correct Answer)     c, Value Axis
6. ________ chart has the shape of round pie cut into pieces or slices.
    a. Scatter     b. Pie(Correct answer)     c. Bar

B. Write 'T' for True and 'F' for False statements.
1. Operators specify the type of calculation we want to perform.      True
2. Formula bar shows the formula we are using in active cell.          True
3. A formula is a readymade operation to perform a calculation.      False
4. Data Series is a caption which identifies the category of axis.      False
5. The horizontal and vertical lines on the plot area are gridlines.    True
6. Area chart shows the correlation between two value sets.           False

C. Fill in the blanks.

1. _______ and _____ are two ways of entering data in excel.

Ans.: Constants, Formulas.

2. Excel recognizes any data as _____ when it begins with an equal to sign(=).

Ans.: Formulas

3. ________ is used to change the order in which Excel performs calculations.

Ans.: Parenthesis

4. A formula using the ______ cell reference keeps the cell reference constant.

Ans.: Arguments

5. Charts are made up of a variety of elements, also called data_____.

Ans.: Objects.

6. ________ are tiny, word-sized charts that can appear in a cell.

Ans.: Sparklines

D. Differentiate between the following:

1. Formula and Function.

Formula	Function
1. Formula is an expression that builds of Constants, Cell References and Operators to perform Calculation.	1. Function is a built in Formulas available in Microsoft Excel that performs calculation.
2. Example of Formula is, =A1+B1+C1.	2. Example of Formula is =SUM(A1,B1,C1).

 2. Absolute Referencing and Relative Referencing.

Relative Referencing	Absolute Referencing
1. While copying formula, if excel changes cell references, then that technique is called as Relative Referencing.	1. While Copying Formulas, if excel keep the cell references intact, that it is called as Absolute Referencing.
2. Here normal column name and row address is used to refer cell address.	2. Here dollar ($) sign is used to refer cell address.
3. Example of formula using Relative Referencing, =A1+B1+C1	3. Example of formula using Absolute Referencing, =$A$1+$B$1+$C$1

E. Answer in 1-2 Sentences.

1. What are operators?

Ans.: Operators are symbols that represents the method of calculation. Example for Addition, + (plus). Some extra examples of operators are, - (Minus), *(Product) etc.

2. Name any four functions of Excel.

Ans.: Four name as example of Function are, SUM, PRODUCT, MAX, MIN etc.

3. What do you understand by cell reference?

Ans.: Every cell in a worksheet has a unique address, is called as Cell Reference. Cells are identified by column name and row number. For example, A1 is a cell reference.

4. What are sparklines in a chart?

Ans.: Sparklines are tiny, word-sized charts that can appear in a cell. Excel includes three types of Sparklines, LINE, COLUMN, WIN/LOSS.

F. Answer Briefly: Each will carry three marks.

1. Explain order of calculation in Excel?

Ans.: When a formula contains more than one operator, Excel performs the calculations in a specific order according to precedence. The order in which Excel performs operations in formulas is called order of calculation. In Excel the order of calculation is as follows:

1. Percent (%)
2. Exponentiation (^)
3. Multiplication (*) and Division(/)
4. Addition (+) and Subtraction (-)
5. Comparison operators   

2. Write the steps to edit a formula?

    Ans.: Steps are,

    1.    Click on the cell where you want to enter a formula.

    2.    Type an equal to sign (=) to begin typing formula.

    3.    Type the formula and then press the ENTER button of the keyboard.

3.. Explain the structure of a function?

Ans.: A function is a built in formula that you can use to perform a calculation on the data in your worksheet.

Function in Excel always begins with equal sign (=) followed by the function name. Function name is followed by opening parenthesis then arguments separated by comma ( , ) or colon ( : ) and their closing parenthesis.

4. What is chart and write its advantages in Excel?

Ans.: Chaarts are graphical representations of the data. Excel offers a wide selection of charts, which displays data in a pictorial way,

Advantages are, 

    1.    Charts displays a lot of information in an  easy to understand format.

    2.    Data and information can be presented in an attractive manner with the help of a chart.

    3.    A chart is more impressive and informative as compared to a simple data statement.