: MS-Excel
CHAPTER 2
CELL REFERENCING, RANGES &
FUNCTIONS
Section 1: Cell References
Learning Objectives
•Learn Cell references and its types,
•Range and range names,
•Entering Formula and
•Copying a Formula
Cell References
Cell references Refer to values in
A10 the cell in column A and row 10
A10,A20 cell A10 and cell A20
A10:A20 the range of cells in column A and rows 10 through
20
B15:E15 the range of cells in row 15 and columns B through E
A10:E20 the range of cells in columns A through E and rows
10 through 20
Cell references can indicate particular cells or cell ranges in columns and
rows.
Cell Referencing
A reference identifies a cell or a range of cells on a worksheet and
tells Microsoft Excel where to look for the values or data you want
to use in a formula.
With references, the user can use data contained in different parts
of a worksheet in one formula or use the value from one cell in
several formulas.
The user can also refer to cells on other sheets in the same
workbook, and to other workbooks. References to cells in other
workbooks are called links.
Reference types
Relative
references
change as they
are copied.
Absolute
references stay
the same as they
are copied.
Relative Reference
Relative Every relative cell reference in a formula
automatically changes when the formula is copied down a
column or across a row. This is why in the first lesson you could
copy the January formula to add up February expenses. As the
example, when the formula =C4*$D$9 is copied from row to
row, the relative cell references change from C4 to C5 to C6.
Absolute Reference
Absolute An absolute cell reference is fixed. Absolute
references don't change if you copy a formula from one cell to
another. Absolute references have dollar signs ($) like this:
$D$9. As the art shows, when the formula =C4*$D$9 is copied
from row to row, the absolute cell reference remains as $D$9.
Mixed Reference
Mixed A mixed cell reference has either an absolute
column and a relative row, or an absolute row and a
relative column. For example, $A1 is an absolute reference
to column A and a relative reference to row 1. As a mixed
reference is copied from one cell to another, the absolute
reference stays the same but the relative reference changes.
Ranges
A range is a rectangular group of cells. The smallest range
is a single cell and the largest range includes all the cells in
the worksheet. A range can include cells from same sheet or
cells from adjacent sheets. Ranges are defined by the
addresses of two opposite or diagonally paired corner cells
separated by a colon or two dots.
Naming Ranges
You can apply a name to refer to a cell or a range of cells, rather than
using cell addresses as references. Names provide multiple benefits:
1.Names are more descriptive and easier to remember than cell addresses.
2. When a cell moves, the name moves with it.
3. You can use a name in place of a cell or range address in a formula or
function argument, just like a row or column label.
4. When you copy a formula that uses a name, the effect is the same as
using an absolute cell reference.
Rules for Range Names
1. Names can be tip to 255 characters long and can include letters, numbers,
underscores, or periods.
2. The name must begin with either a letter or the underscore character. You
cannot use spaces, commas, exclamation points, or other special characters.
3. Names cannot be valid cell addresses: F1998 cannot be used as a name.
4. Names are not case sensitive.
Using Range Names in Formula
1. Start Excel.
2. Create the above worksheet:
3. Highlight the figures in the Delhi column.
4. Click in the name box at the top of the spreadsheet. The name box
normally contains the address of the current cell.
5. Type Delhi in the name box and press Enter. The range of numbers is
now identified by the name Delhi.
6. Click in the Totals cell for Delhi to select it.
7. Type: =SUM(Delhi)
Using Range Names in Formula
You can also use names in formulas. For
example:
=SUM(JanSales)
=TotalSales * TaxRate
Using Range Names in Formula
Exercise:
You can assign any
name you want as
follows:
Select one or more
cells and choose
Insert > Names >
Define.
Type the name, click
Add, and click OK.
Then you can create
formulas like this.
Formulas
A formula always begins with an equal sign (=) followed
by some combination of numbers, text, cell references,
and operators. If a formula is entered incorrectly, an
ERROR IN FORMULA message will appear.
Formulas Operators
• To do more than add, use other math operators
as you type formulas into worksheet cells. Use
a minus sign (-) to subtract, an asterisk (*) to
multiply, and a forward slash (/) to divide.
Remember to always start each formula with an
equal sign.
Parts of a formula
How to enter a formula
1. Click a cell where you want to enter a formula.
2. Type = (equal sign) to begin the formula.
3. Type the first argument. Remember, an argument can be a
number or a cell reference. You can type in the number or if
referencing a cell, you can single click on the cell location to
have the cell reference automatically included in your formula.
4. Next, type an arithmetic operator.
5. Next, enter the next argument.
6. Steps 4 and 5 can be repeated as many times as needed to add
to the formula.
7. Last, tap the ENTER key. The result of the formula appears in
the cell while the formula itself appears in the Formula Bar.
Auto Sum
To quickly add a
column of data
together, use the SUM
button. To use this
feature, select the
numbers you want to
add together by
clicking and dragging,
then click on the SUM
symbol.
Sum button
click in cell B7, and then:
On the Home tab, click the
Sum button in the Editing
group.
A color marquee
surrounds the cells in the
formula, and the formula
appears in cell B7.
Press ENTER to display the
result in cell B7.
Click in cell B7 to display
the formula in the formula
bar.
Functions
Learning Objectives
•To learn about different type of functions,
•Mathematical functions,
•Logical function, text functions and
•Statistical functions,
•Formula auditing
•Error messages in formula
Functions
Excel includes hundreds of other functions that you can use to calculate results
used in statistics, finance, engineering, Maths, and other fields.
Functions are structured programs that calculate a specific result: a total, an
average, the amount of a monthly loan payment, or the geometric mean of a
group of numbers. Each function has a specific order or syntax that must be used
for the function to work, properly.
Functions are formulas, so all functions begin with the equal sign (=). After that
is the function name, followed by one or more arguments separated by commas
and enclosed in parentheses:
Example:
= SUM(D6:D11)
Functions
Excel’s functions are grouped into 10 categories
Using Functions
Rather than typing out the whole formula, there
are shortcuts to creating commonly used
formulas. The formula “=B2+B3+B4+B5” can be
represented as a functions: “=SUM(B2:B5)”.
Financial Functions
Financial Functions
NPV Function
Calculates the net
present value of an
investment by using
a discount rate and
a series of future
payments (negative
values) and income
(positive values).
Financial Functions
PMT Function
Calculates the
payment for a loan
based on constant
payments and a
constant interest
rate.
Financial Functions
RATE Function
Returns the interest
rate per period of an
annuity. RATE is
calculated by
iteration and can
have zero or more
solutions.
Financial Functions
FV Function
Returns the future
value of an
investment based
on periodic,
constant payments
and a constant
interest rate.
Financial Functions
PV Function
Returns the present
value of an
investment. The
present value is the
total amount that a
series of future
payments is worth
now.
Mathematical & Statistical Functions
Mathematical Functions
SUM Function
The Excel SUM
function returns the
sum of one or more
numerical values.
Mathematical Functions
SUMIF Function
Tests the cells in a
supplied range
according to a given
criteria, and for those
cells satisfying the
criteria, returns the
sum of the
corresponding cells in
a second supplied
range.
Mathematical Functions
ROUND Function
Rounds a supplied
number up or down,
to a specified number
of digits
Mathematical Functions
INT Function
Rounds a supplied
number down to a
whole integer
Mathematical Functions
FLOOR Function
Rounds a supplied
number down to a
supplied significance
Date and Time Functions
Date and Time Functions
DATE Function
Returns a date, from a
user-supplied year,
month and day
Date and Time Functions
NOW Function
Returns the current
date and time
Date and Time Functions
WEEKDAY Function
Returns an integer
representing the day
of the week for a
supplied date
Statistical Functions
AVERAGE Function
Returns the AVERAGE
of a list of supplied
numbers
Statistical Functions
AVERAGE Function
Returns the AVERAGE
of a list of supplied
numbers
Statistical Functions
COUNT Function
Returns the number
of numerical values in
a supplied set of cells
or values.
COUNTIF Function
Returns the number
of cells (of a supplied
range), that satisfy a
given criteria.
Statistical Functions
MAX Function
Returns the greatest
value from a list of
supplied numbers.
MIN Function
Returns the smallest
value from a list of
supplied numbers
Logical Functions
AND Function
Tests a number of
user-defined
conditions and returns
TRUE if ALL of the
conditions evaluate to
TRUE, or FALSE
otherwise
Logical Functions
IF Function
Tests a user-defined
condition and returns
one result if the
condition is true, and
another result if the
condition is false
Logical Functions
OR Function
Tests a number of
user-defined
conditions and returns
TRUE if ANY of the
conditions evaluate to
TRUE, or FALSE
otherwise
Lookup Functions
LOOKUP Function
Searches for a specific
value in one data
vector, and returns a
value from the
corresponding
position of a second
data vector
Lookup Functions
LOOKUP Function
Result -
Lookup Functions
VLOOKUP Function
If supplied with a
value from one
column of a table,
vlookup returns the
corresponding value
from another column
Lookup Functions
VLOOKUP Function
Result
Text Functions
CONCATENATE
Function
Joins together two or
more Strings
Text Functions
FIND Function
Returns the position
of a supplied
character or text
string from within a
supplied text string
(case-sensitive)
Formula Error Messages
If a formula is
incorrectly entered in
an Excel worksheet,
an error message is
displayed.
Formula Auditing
The process of
examining a
worksheet for errors
in formulas is referred
to as auditing.
Questions
1. An excel formula always begin with ___
2. Excel performs operation in an equation from
___
3. To divide 853 by 16 in a formula in Excel, you
would use what math operator?
4. ___ references change when copied.
5. When you instruct Excel to divide by Zero you
get ___
Answers
1. An excel formula always begin with _=__
2. Excel performs operation in an equation from
__R->L_
3. To divide 853 by 16 in a formula in Excel, you
would use what math operator? Ans: /
4. Relative references change when copied.
5. When you instruct Excel to divide by Zero you
get __#DIV/0!_
Exercise 2
In this exercise, you will be required to use Name Jan Feb March
an Excel spreadsheet to answer the Abdul 310.56 403.10 384.10
following questions: James 431.72 342.00 344.00
Samuel 600.00 299.18 402.17
1. Use the table entered in exercise 1 Lydia 456.07 466.00 610.00
Tina 201.10 342.19 334.04
2. Using functions, find the Maximum,
Minimum and Average expenditure of
each person.
