# PSEB Class 9 Computer Science Chapter 5 MS Excel Part – 3 Solution

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

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

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

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

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

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.