Ms EXCEL PRACTICAL (PART TWO)

A function is inserted into a spreadsheet either by typing it directly into the active cell; or
in the formula bar; or by using the INSERT FUNCTION option in Excel. The latter
automates the process, ensuring that you get arguments in the right order. It also
provides links to the Help page (which includes examples of how the function is used).
There are several ways of accessing the INSERT FUNCTION dialog box:

i) Use the shortcut – SHIFT + F3

1
ii) Click on the INSERT FUNCTION icon next to the formula bar.

2
USING THE WIZARD FUNCTION

Make sure you are in the cell where you want to place a function, then open the
INSERT FUNCTION dialog box by one of the methods listed above.
The INSERT FUNCTION dialog box will appear as below:

SHORTCUT FOR ENTERING A FUNCTION

You can access your most recently used functions without having to go through the
INSERT FUNCTION dialog box.

Make sure you are in the cell where you want the function to be. Instead of clicking
on the FX icon to start your function, type an equal sign (=). You will see the
function that was last used in the space where the cell address normally shows.
Either click on the function name (if it is the one you want to use), or click on the
drop down arrow next to the function name to see the list of recently used
functions.

3
4
As soon as you click on the required function, you will go straight to the
FUNCTION
ARGUMENTS dialog box, bypassing the INSERT FUNCTION option.

5
 RELATIVE & ABSOLUTE CELL REFERENCING TECHNIQUES

There are different sorts of cell references. These are:

• Relative
• Absolute
• Mixed
A relative reference will change column and row numbers as it is copied to other cells.
Think of it as an original instruction being to go in a certain direction e.g. 2 rows up and
2 columns across from your current position. When this instruction is copied elsewhere,
it will still refer to 2 rows up and 2 columns across from your new current position.

An absolute reference is one that does not change. If you refer to a cell in a certain row
and column and then copy that reference elsewhere, it will still refer to exactly the same
cell or range.

A mixed reference is one that is half relative and half absolute. An absolute address is
defined with the use of the “$” symbol. This can be typed in at the time of creating the
formula, or by editing the cell afterwards.

6
Examples of relative and absolute addressing:
Relative A4 A4:B5
Absolute $A$4 $A$4:$B$5
Mixed $A4 or A$4 $A4:B$5

The “$” sign can be hand typed or inserted by pressing the keyboard shortcut F4. As
you press F4 the cell address will cycle between the four variations of an address – for
example A4 would cycle between:
A4
$A4
A$4
$A$4

WORKING WITH CONSTANTS/NAMING CONSTANTS

Lets assume that we have a member of staff working in an organization where leave
allowance calculated as a constant percentage. To work with constant load it a cell i.e
H5 so that you can use this value in a formula. By having the value in a separate cell, if
the rate changes all formulas referring to this cell would update.

7
The formula in this case is =(D5/52)*4*$H$6, i.e. the salary divided by 52 weeks,
multiplied by 4 weeks and then multiplied by 2.0%.

Autofilled for the rest of staff

8
When we change the rate

Lets change the rate to 10%

9
N/B; Note the changes in the leave allownaces earnings while the formula remains
constant.

10
 USING THE IF COMMANDS

Lets add two columns on our previous data on students DCU 102 marks and label them
Exam marks and Remarks respectively.

Lets add some fictitious Exam marks taking 50 as the optimum score.

11
N/B; Note the changes in the total score when you key in the exam marks the formula
pick and get the summation of the entire marks.

THE IF FUNCTION APPLIED

Lets set a conditional If command in the remarks column i.e simple ‘PASS’ and ‘FAIL’.
Lets set parameters that any student who score less than 65 marks is a FAIL and above
the mentioned marks is a PASS. The formular will be; =IF(J4>=65, " PASS", "FAIL") or
=IF(J4<=65," PASS","FAIL")

12
13
APPLICATION OF NESTED IF

Nested IF means hosting the IF FUNCTION into an original IF . Lets attach some
Grades to the scores to apply the nested IF function.
Add a new column and label it Grades;
Allocate grades as below;
A >=70
B>=65
C<=60
D<50

USING THE FILTER FUNCTION

The filter function is applied when you want/desire to view a specific selection. For
instance if we want to filter the data above only to view the pass remarks then the steps
will include;

Select the label rows

14
Under the editing group

Click on sort & filter

15
Click on filter on the drop down menu

Click on the drop down arrow under the remarks column

Click OK

Uncheck all the other check boxes except PASS check box.

16
17