Excel 2000 Module 3
Formulas
& Functions
With
Excel, it's easy to perform common calculations. In addition
to adding, subtracting, multiplying, and dividing, you can calculate
the total and compute the average of a set of values.
Click <File>
<Open>
Practice M2Ex
Click <Sheet1>
Rename <Sheet1>
to <M2Ex>
Click
Cell
A7
Type
Total
 Formula
Concepts
A
formula is the written expression of a calculation to be performed
by Excel. When you enter a formula into a cell, the formula is
stored internally while the calculated result appears in the
cell.
Excel
Mathematical Operators
(.......) any computation in the
bracket;
^
exponential such
as
3^2 is 3²;
* multiplication
such
as
3*2;
/
division such
as
3/2;
+ addition such as 3+2;

subtraction such
as
32.
Operators
and Priority Rules
Calculation
in Excel follows the Priority Rules in the Operators
(
); ^ ; * or / ; + or 
Priority
1 : ( )
Priority
2 : ^
Priority
3 : *
or / firstcomefirstserve
Priority
4 :
+ or  firstcomefirstserve
e.g. =1+2*3+(2*3/3)
Step
1
(Priority
1): 2*3 first
then divided
by 3
Step
2 : 2*3 + Step 1
sub total
Step
3 : 1 + Step 2
sub total
 Creating
Formulas
Creating a formula is similar to entering text and numbers in
cells. To begin, you select the cell in which you want the formula
to appear. You
can use one of two methods to create the formula.
In
the first
method,
you type the formula, including cell addresses, constant values,
and mathematical operators, directly into the cell. To mark the
entry as a formula, you start by typing an equal = sign.
Click Cell
B8
Type at the Formula bar B5+B6
In
the second
method,
you start by clicking an equal
= sign (<Edit>
button at the Formula bar), then paste the references for
a cell or range of cells in the Formula bar. You then complete
the formula by typing any operators, constant values, or parentheses.
When Excel is in Edit mode, three buttons appear to the left of
the Formula bar :
<Cancel>, <Enter>, and <Edit>.
Click
Cell
C8
Click Cell C5
Type +
Click Cell C6
Click <Enter>
on the Formula bar
 Copying
Formulas
When you change your mind about the placement of the Formula
of a cell, you can change
the way you've placed data in your worksheet.
Copy & Paste, Cut & Paste
Alternatively
Use
Clipboard
Menu, Point
and click <View><Toolbars>
:
click
Clipboard
Alternatively
Use
Fill Handle
When you select a cell with a formula and drag the Fill handle, Excel
changes the cell references in the formula to match those of
the column or row to which it has been copied.
Select
Cell
C8, drag
Fill
handle to D8
 Editing
Formulas
Editing a formula that you have already created is easy and similar
to editing the contents of any other cell.
Doubleclick the
cell,
type your
changes
directly in the cell, and <Enter>
When
Excel is in Edit mode, three buttons appear to the left of
the Formula bar :
<Cancel>, <Enter>, and <Edit>.
Click the cell, click
in
the Formula bar,
type your
changes,
click <Enter
or => button on the Formula bar.
Click the
cell,
click <Edit
or => Formula button, type your changes in the Formula
bar,
click <Enter>
button on the Formula bar.
 Using
Absolute and Relative Cell References
An absolute
reference
refers to the address of a specific cell. A relative reference refers
to a cell that is a specific rows and columns from the cell that
contains the reference.
Click
<Sheet2>
Rename <Sheet2>
to <Mixed Addressing>
Practice M3Ex
Click Cell C4
Create the Formula
You will notice how the relative cell referencing error will
happen when you copy or use Fill
handles on
the formulas.
Do you notice how the absolute cell referencing for column B
does when you copy formulas across.
Use <F2> key to
examine the formula
Press <Esc> to quit
To correct the error, we edit the formula.
Example :
B1 (Column B Row 1)
$B$1 absolute
reference
Use <F2> key to
examine the formula
Press <Esc> to quit
No error if we copy formula across or downwards
Example
:
B4 (Column B Row 4)
$B4 absolute
reference to Column B, but relative reference to Row 4
Use <F2> key to
examine the formula
Press <Esc> to quit
No error if we copy formula across
Example
:
C3 (Column C Row 3)
C$3 relative
reference to Column C, but absolute reference to Row 3
Use <F2> key to
examine the formula
Press <Esc> to quit
No error if we copy formula downwards
 Using
the AutoSum or the SUM Function
A function is a predefined formula that performs a common or
complex calculation. A function consists of two components :
(1) function
name;
(2) argument
list enclosed in ( ).
Depending on the function, an argument can be a constant value,
a singecell reference, a range of cells, a range name, or even
another function. When a function contains multiple arguments,
the arguments are separated by commas.
Click
<Sheet3>
Rename <Sheet3>
to <AutoSum Example>
Use the AutoSum Example at M3: Page 6
Example : =SUM(B4:B6)
Click Cell B7
Click <AutoSum>
button
The range B4:B6 is highlighted
Click
<Enter>
at the Formula bar
Use
Fill handles at
Cell B7 to drag the formula to E7.
Using
Date Functions
Excel's date and time functions allow you to use dates and times
in formulas using the functions of DATE ,TIME ,NOW and TODAY.
Click
Cell
A10
Type Before
is:
Click Cell B10
Click <Edit
or => button at the Formula bar
Click <Function>
button
Click <DATE>
The Formula Palette appears.
Type <Year>
box 2000
Type <Month>
box 2
Type <Day>
box 29
Click Cell A11
Type Today
is:
Click Cell B11
Click <Edit
or => button at the Formula bar
Click <Function>
button
Click <TODAY>
The Formula Palette appears.
Click
<OK>
 Using
the Formula Paste Functions
The Formula Paste Functions offers a third option of entering
Formula. The Formula Palette lists each function and its arguments,
a description of each function and its arguments, and the calculated
result of each function and the overall formula.
Click
<Sheet4>
Rename <Sheet4>
to <Simple Paste Function>
Click Cell C11
Click
<Edit or => button at the Formula bar
Click <Function>
button
Click <SUM>
The Formula Palette appears.
The Sum function total cells C1:C10
Click <OK>
Click Cell C12
Click <Edit
or => button at the Formula bar
Click <Function>
button
Click <COUNT>
The Formula Palette appears.
Click <Expand>
Select Cells
C1:C10
The COUNT function counts cells C1:C10
Click <OK>
Click
Cell
C13 or C14 or C15 respectively
Click <Edit
or => button at the Formula bar
Click <MAX>
or <MIN>
or
<AVERAGE>
The Formula Palette appears.
Click <Collapse>
button at Number 1 box
Select C1:C10
Click <OK>
 Auto
Calculate
On the Status bar at the bottom of the screen, by position the
mouse cursor with a rightmouse
click,
other function commands like Average, Count, Max, Min and Sum appear.
Using
the COUNTIF (Range, Criteria)
Using the COUNTIF function to count the number of cells within
a range which meets the given criteria.
Click
Cell
B17
Click <Edit
or => button at the Formula bar
Click <Function>
button
Click <COUNTIF>
The Formula Palette appears.
Click <Collapse>
button at Number 1 box
Select C1:C10
Click at Number
2 box
Type ">=50"
Click <OK>
 Using
the IF Function
Using the IF function creates a conditional formula. The result
of a conditional formula is determined by the state of a specific
condition or the answer to a logical question.
The IF funcion requires the following syntax:
IF(Logical_test, Value_if_true, Value_if_false)
Logical
test 
expression to be evaluated as true or false
Value_if_true  value
returned if the logical_test expression is true
Value_if_false  value
returned if the logical_test expression is false
Click
<Sheet5>
Rename <Sheet5>
to <IF Example>
Use the IF Example at M3: Page 12
We will determine the PASSED or FAILED grading for the students.
Click
Cell
C2
Click <Paste
function> button
Click
<Logical>
Select <IF>
Click <OK>
Type B2<50
at the Logical Test box
Type "FAIL"
at the Value_if_true box
Type True at
the Value_if_false box
Click <OK>
 Using
the VLOOKUP Function
Using the function to search for a value in the leftmost column
of a table, and then returns a value in the same row from a column
you specify in the table.
=VLOOKUP(LOOKUP
VALUE,
COMPARE
ARRAY,
COLUMN
INDEX)
Click
<Sheet6>
Rename <Sheet6>
to <Vlookup Example>
Use the VLOOKUP Example at M3: Page 14
Click Cell C7
Click <Paste
function> button
Select <VLOOKUP>
Select B7 at
the Lookup_value box
Select B1:C4
at the Tablearray box
Edit $B$1:$C$4
Type 2 at the
Col_index_num box
Click <OK>
 Using
the HLOOKUP Function
Using the function to search for a value in the top row of a
table or an array of values, and then returns a value in the
same column from a row you specify in the table.
=HLOOKUP(LOOKUP
VALUE,
COMPARE
ARRAY,
ROW
INDEX)
Click
<Sheet7>
Rename <Sheet7>
to <Hlookup Example>
Use the HLOOKUP Example at M3: Page 15
Click Cell C7
Click <Paste
function> button
Select <HLOOKUP>
Select B5 at
the Lookup_value box
Select B1:E2
at the Tablearray box
Edit $B$1:$E$2
Type 2 at the
Row_index_num box
Click <OK>
Click <File>
<Save>
Click
<File>
<Close>
Practice
Exercises
Click <File>
<New>
<Save As>
My
Second Excel2000
at
<My Documents> folder
Practice
Exercise 1 :
M3:
Page 17
Click
<Sheet1>
Rename <Sheet1>
to <M3Ex1>
Practice
Exercise 2 :
M3:
Page 18
Click
<Sheet2>
Rename <Sheet2>
to <M3Ex2>
Practice
Exercise 3 :
M3:
Page 19
Click
<Sheet3>
Rename <Sheet3>
to <M3Ex3>
Practice
Exercise 4 :
M3:
Page 20
Click
<Sheet4>
Rename <Sheet4>
to <M3Ex4>
Click
<File>
<Save>
Click
<File>
<Close>
Click
<File>
<Exit>
Practice
Project 1
Practice
Excel
2000 Project1
Click
<File>
<Save>
Click
<File>
<Close>
Click
<File>
<Exit>
Edwin
Koh : We
completed on the New
Knowledge and Skills in
Excel
2000 Module 3.
