Chapter - 3
More on MS Excel 2007 – Formulae and Functions
1.
|
What do you understand by the
term Conditional Formatting?
|
This feature allows us to format the data in the
worksheet depending on if it satisfies a certain condition.
|
|
2.
|
What are functions?
|
Functions are predefined commands that act on a cell
or range of cells and perform some particular operation quickly.
|
|
3.
|
Define the term filtering?
|
Filtering is a quick way to find a set of data in a
range.
|
|
4.
|
What is cell referencing?
|
Cell referencing means how a cell address behaves in
the formulae when it is copied from one cell to the other.
|
|
5.
|
What do the following functions
do? Explain with proper syntax and example of each.
|
a) SUM:
It adds all the numbers in
a given range of cells.
Syntax: =SUM(range or
numbers)
Example: =SUM(A1:A5)
b) AVERAGE:
It calculates the average
of the numbers in a given range of cells
Syntax: =AVERAGE(range or
numbers)
Example: =AVERAGE(A1:A5)
c) MIN:
It returns the smallest
value in a given range of cells.
Syntax: =MIN(range or
numbers)
Example: =MIN(A1:A5)
d) MAX:
It returns the largest
value in a given range of cells.
Syntax: =MAX(range or
numbers)
Example: =MAX(A1:A5)
|
|
6.
|
What is the use of IF function?
Explain with an example.
|
The IF function checks to see if a condition you
specify is true, or false. The IF function returns the first value if the condition specified
evaluates to TRUE and the second value if it evaluates to FALSE.
Syntax: =IF(condition, value_if_True, value_if_False)
Example 1: =IF(A1>50, 150,100)
Example 2: =IF(A2=100, A2*10,”LOW”)
|
|
7.
|
What is Sorting? In how many ways you can sort
your data?
|
Sorting means to arrange data either in ascending or descending order.
Excel allows you to sort by date, sort by text or numbers, sort by multiple
columns, or by colour. And, if you like, you can even colour code your data.
|
|
8.
|
Define the following with an appropriate example
of each.
|
a)
Relative referencing: in this referencing both
column part and row part are not fixed. E.g. if the formula in D4 is =B4*C4,
when the formula is copied vertically notice that the cell address in the
formula is automatically changed. (Pg. No 36)
b)
Absolute referencing: in this referencing both
column part and row part are fixed.
E.g. if the formula in D4 is =$B$4*2 and when it is copied to the
lower rows, there is no change in the cell address. (Pg. No 37)
c)
Mixed referencing: in this referencing
either column part or row part is fixed.
E.g. $B4 is a mixed cell reference with absolute column and relative
row and C$4 is a mixed cell reference with relative column and absolute row.
(Pg. No 37)
|
|
9.
|
Write the steps to apply Custom Filter on a range
of cells.
|
Step 1: Click Data tab – filter
Step 2: Click the arrow in the column heading.
Step 3: Select Number Filters from the list.
Step 4: Click Custom Filter, Custom AutoFilter dialog box appears.
Step 5: Choose the option according to the condition you want.
Step 6: Too add another criteria, click Add
Step 7: Specify the second condition in the same way.
Step 8: Click OK button to view the rows according to the specified
conditions.
|
|
