**PSEB Punjab Board Class 9 Computer Science Textbook Solution Chapter 5 MS Excel Part – 3 Exercise Questions and Answers**

**Q1) Multiple Choice Questions :-**

**1) Each functions in formula must start with ……………………….. symbol in MS Excel**

a) + b) = c) & d) ^

**Answer – b) = **

**2) Which function of MS Excel can be used to find minimum numbers from given range?**

a) MINIMUM b) MID c) MIN d) None of these

**Answer – c) MIN**

**3) Ampersand (&) symbol is an alternate of ………………….. function in MS Excel**

a) SUM b) AND c) CONCATENATE d) POWER

**Answer – c) CONCATENATE**

**4) Which data tool can be used to have only distinct values in a particular column?**

a) Data Validation b) Text to Column c) Formula d) Remove duplicates

**Answer – a) Data Validation**

**5) Which one is an example of Arithmetic Operators?**

a) + b) % c) ^ d) All of these

**Answer – d) All of these**

**Q2) Write True or False :-**

1) We cannot count blank cells in MS Excel, (**False)**

2) Formula is an expression of operators and operands to perform calculations. (**True)**

3) SUM function can be used to perform addition of values in a particular range. (**True)**

4) Text to column option can be used to split our contents in multiple cells. (**True)**

5) NOW function returns current data and time in MS Excel. (**True)**

**Q3) Short Answer Type Questions :-**

**1) Write arithmetic operators being used in MS Excel**

Ans :- Addition ( + ), Subtraction ( – ), Multiplication ( * ), Division ( / ), Percent ( %), and

Exponentiation ( ^ ) are some of the arithmetic operators which are used in MS Excel.

**2) What do you mean by Data Validation.**

Ans :- Data Validation Rule helps to set up the database table and to make a control

over it like what kind of data should be entered in the cells. Once the data validation

rule is been applied, you must make sure that whatever the data you enter into the

cell is correct/right. If incase you make a mistake while entering the data in the cell

like instead of entering the numerical data you had entered the text data, then it will

show the error message in the box and will suggest what type of data should be

entered into the cell.

**3) Give the name of any three mathematical functions.**

Ans :- SUM , MIN , AVERAGE are the some functions which are used for performing

mathematical operations.

**4) What is sorting in MS Excel.**

Ans :- Sorting means to arrange the contents of one or more cells of the rows and

columns in a proper order/in a sequential manner.

**5) Define Formula**

Ans :- Formula is the sum/ equation which is used to solve arithmetical problems,

calculations. Formulas are entered in the cell, and every formula must start with = (is

equal to sign). If we don’t add the = (is equal to sign) at the beginning of the formula

then we not get desired result. Formulas are totally depend upon the operand/maths

symbols in between the values. We can also make use of formula in doing basic

calculations in addition, subtraction, division and multiplication.

**6) Provide the name of various conditional functions used in MS Excel.**

Ans :- The various conditional functions which are used in MS Excel are IF function,

SUMIF function, COUNTIF function.

**Q4) Long Answer Type Questions :-**

**1) What is Cell Referencing? Explain its types**

Ans :- In a Excel worksheet, each cell is bee identified/recognized by an unique

address that is called as cell reference. In Excel, cells are by default identified by

column letter and row number. If cell A5 is active i.e means the active cell is in

column A and 5th row. While entering formula in the worksheet, the cell reference is

taken into consideration/use instead of making use of actual data. For ex Formula

=A1 + B1 instead of = 10 + 20. When you use cell references and you change a

number used in a formula, Excel will automatically redo the calculation for you. At the

time of making use of formula the cell is bee referred, where the cell will be referred

during calculation time. The cell will be referred (cell address) when the formula is

been added. There are three types of cell referencing which are i) absolute cell

referencing ii) Relative cell referencing and iii) Mixed cell referencing

a) Relative cell referencing :- In MS Excel, this is the by default cell referencing.

When the cell/cells are in relative references, the location copied to other also

changes which depends on the relative positions of rows and columns. For ex:-

If we make a copy of the formula that is =A1+B1 from row 1 too row 2, then

the formula gets automatically changed into the =A2+B2. If incase, we want to

add the same calculation or formula in the multiple rows and columns, then

making use of relative cell reference is essential

**b) Absolute cell referencing :-** When this cell reference is been applied, the cell

reference does not change even if the contents of the cell/cells is copied or

filled. Absolute reference does not change its cell/cells contents when the

copied or filled. If we want or keep the contents of the cell/cells in the same

manner, not changing then we should make use of Absolute cell referencing.

In the formula you may see the appearance of dollar sign $ before the column

and row. The absolute refence will not change its contents even if the cell is

been dragged.

**Note :-** The rows and columns having particular references remain fixed, if we

ware using this type of cell referencing.

**c) Mixed cell referencing :-** This cell reference includes both that is absolute and

relative references as if while copying, out of row and column only one

remains fixed. Such type of references are made used when the tasks is

related to a particular row or column. In simple words, it can be explained as

mixed cell refencing locks the cells in such a manner that there is not change

in row and column even if it is copied.

In the above diagram, you may notice the dollar sign are fixed with the column

A first reference and row 1 of second reference. If incase the particular cell is

filled in other cells of different rows and columns, the rows and columns are

fixed, and unchangeable.

**2) Define any 3 string Functions.**

Ans :- The three string functions are explained below :-

**a) LEN function :-** LEN is the short abbreviation of LENGTH function. This function is

helpful in finding out the overall characters numbers including if any spaces or

symbols are containing. The function can be used as = LEN (<value of the string>)

**b) LOWER function :-** If incase you want to convert the characters of the string

(text) into the lower case (small alphabets/letters), then you may make use of

this function. The function can be used as = LOWER (<value of the string>).

**c) UPPER function :-** If incase you want to convert the characters of the string (text)

into the upper case (big alphabets/letters), then you may make use of this

function. The function can be used as = UPPER (<value of the string>).

**3) What is Function? Explain any two mathematical functions with suitable example.**

**Ans :-** Function is a pre-defined formula in Excel which is used to work /perform

calculations on the data that is entered in your worksheet. This formula performs the

operations of calculations on the values that are in series. In worksheet you can work

with mathematical calculations by making use of 300 functions which is provided by

Excel. Some of the commonly used functions are SUM, AVERAGE, COUNT, MAX, MIN

etc. In Excel, every function has a name such as to sum the data is called SUM, for

averaging values AVERAGE function is used where as for finding minimum or

maximum values from the series of data we make use of MIN and MAX functions.

There are 11 categories of functions that are grouped in Excel which are Cube, Date

and Time, Engineering, Financial, Information, Logical, Lookup & Reference,

Mathematical & Trigonometric, Statistical, Text, Database.

*Dear Student, I appreciate your efforts and hard work that you all had put in. Thank you for being concerned with us and I wish you for your continued success.*

**For more update, follow this page ****⇒** **PSEB Notes and Solutions**