Chapter Two 1

Using Excel for Business Applications

Chapter Two

Basic Worksheet Formulas and Functions

The power of spreadsheets comes from their mathematical formulas and functions. You can build simple to
very complex formulas in Excel. The results of calculations are tied to the values used so that any change in
these values triggers Excel to automatically recalculate the results.

Functions are Excel’s in-built complex formulas. There are hundreds of them. They save you the trouble of
having to write very complex formulas. This chapter introduces some simple formulas and functions and how
to replicate them.

The equal sign(=) must be entered as the first character in every formula that you enter into a cell. You can
use the plus sign (+) to start a formula but Excel will change it to an equal sign. The equal sign is used to tell
the spreadsheet program that what you are entering is not text but a formula. The following mathematical
operators can be used in a formula:
Excel uses the same rules of precedence of mathematical operators that we learnt in primary schools. That is,

Table 2.1: Mathematical

Operators Used in Excel
Formulas

in a formula, exponentiation will be evaluated first followed by division and multiplication while addition and
subtraction will come last. If you do not wish Excel to follow these rules, then, use brackets.

Operands in an Excel formula can all be numbers such as =60*5+20; they can all be cell addresses such as
=B1*C1-B2 or a mixture of the two such as =B10/60+20.

In entering a formula, you must remember:

 To choose the cell where the results will be inserted by clicking it.
 To signal to Excel that you have finished entering the formula by doing one of the following:
Pressing the Enter key.
Pressing the TAB key.
Pressing any of the cursor movement keys.
Clicking the Enter button on the Formula bar.

@ K V Mgaya University of Botswana

 Chapter Two 2
Using Excel for Business Applications

Activity 2.1: Adding Down and Across

Let us enter a couple of formulas in the worksheet shown in Fig. 1.4:
Click cell B10 and enter the following formula: =B5+B6+B7+B8.
Click cell C10 and enter the following formula: =C5+C6+C7+C8.
Complete the additions for columns D to G.

Let us add across:

Click cell I5 and enter the following formula: =B5+C5+D5+E5+F5+G5.
Complete additions for rows 6 to 8 across putting the results in column I.

Activity 2.2: Adding Down and Across

Enter the information shown in Fig 2.1:
Go to cell D1 and type: Sales
Go to cell D2 and type: Cost of Sales
Go to cell D3 and type: Gross Profit
Go to cell D4 and type: Total Expenses
Go to cell D5 and type: Net Profit
Go to cell E1 and type: =B1*B2
Go to cell E2 and type: =B1*B3
Go to cell E3 and type: =E1-E2
Go to cell E4 and type: =B6+B7+B8+B9+B10
Go to cell E5 and type: =E3-E4

Figure 1
The SUM() Function
One of the heavily used Excel functions is the SUM() function. A function is a pre-supplied formula that you
can use to calculate values. The SUM() function is used to add numbers or numbers in a cell range.

The syntax of a SUM() function is:

=SUM(argument1,argument2, ...)
where
argument1 and argument2 can stand for numbers, cell reference or cell range.

If you type and enter =SUM(2,3,4,5) you will get the answer 14. But the power of the SUM function lies in
adding numbers in a cell range. For example, if you want to add numbers found in cells B1, B2, B3, B4, B5
and B6 you can use the formula =SUM(B1:B6) which is a short-cut for the formula =
B1+B2+B3+B4+B5+B6. Note that we use the colon (:) to specify a continuous range. If you substitute the
colon with a comma Excel will only add together numbers contained in cells separated by commas neglecting
any numbers in cells that lie between. The formula =SUM(B1,B6) is therefore equivalent to =B1+B6 and not
=B1+B2+B3+B4+B5. The formula =SUM(C1:C6,D1:D6) is equivalent to =SUM(C1:C6)+SUM(D1:D6).

@ K V Mgaya University of Botswana

 Chapter Two 3
Using Excel for Business Applications

Activity 2.3: Adding Using the SUM() Function

Let us use the SUM function to add the numbers in Fig 1.4:
Delete any formulas in cell range B10:G10.
Delete any formulas in cell range I5:I8.
Click cell B10 and enter the following formula: =SUM(B5:B8).
Click cell C10 and enter the following formula: =SUM(C5.C8).
Complete the additions for columns D to G.

Entering Cell References by Clicking and by Clicking and Dragging

There is a quicker way to enter cell references in the formula or function than typing them. You can enter the
reference of one cell by clicking the cell. You can enter the references of many cells by clicking and dragging
to cover the cell range and finishing off by hitting the Enter, TAB, or cursor movement keys.

Activity 2.4: Entering Cell References by Clicking and Dragging

Let us repeat what we did in Activity 2.3 above:
Delete any formulas in cell range B10:G10.
Delete any formulas in cell range I5:I8.
Click cell B10 and type =SUM( but do not press the Enter key.
Click A5 and drag down to B8.
Press the Enter key.

Complete the additions for columns D to G using the above method.

Let us add across:

Click cell I5 and enter the following: =SUM( but do not press the enter key.
Click cell B5 and drag across to cell G5.
Press the Enter key
Complete additions for rows 6 to 8 across putting the results in column I.

Adding Using AutoSum

AutoSum is a facility that adds numbers automatically using the SUM() function. It is represented by the
summation icon on the Editing option of the Home tab. AutoSum works by suggesting the cell range to
be added. You may agree with its suggestion or change it. So, do not use AutoSum facility carelessly as you
may end up with unpredictable results. If you click the AutoSum symbol it will first look for numbers which
are above it (in the same column) and add them. If there are no numbers above it, it will look for numbers to
its left (in the same row) and add them. It is always good to indicate the numbers you want to add by choosing
the cell range yourself and then clicking the AutoSum button.

Activity 2.5: Adding Using AutoSum

Let us use AutoSum to add the numbers in Fig 1.4.
Clear any figures in row 10.
Click cell B5. Hold down the mouse button and drag right and then down to cell G10.
Double click the summation icon on the Editing option of the Home tab.

@ K V Mgaya University of Botswana

 Chapter Two 4
Using Excel for Business Applications

Exposing and Editing Formulas in a Cell

Occasionally, you may want to know the formula that gives you a particular figure. One way of
knowing the formula that has been entered in a particular cell is to click the cell and look for the
formula in the Formula Bar. You can then click and edit the formula on the Formula Bar.

An even better way of exposing the formula that has been entered in a particular cell is to double

Fig. 2.2: Double clicking A Formula Cell

Double clicking a cell exposes

the formula on the cell and in
the Formula Bar.

Double clicking the cell will

also show the cells that are used
as operands in the formula.

click on the cell and Excel will expose the formula ready to be edited on the cell itself. This method
also lets you know the cells or cell ranges that have been used in the formula. This is a very useful
facility that lets you know why a formula is not giving you the expected results. See Fig 2.2.

Copying Formulas
We can tell Excel to replicate formulas entered in one or more cells to other cells. Excel will automatically
change the cell references for each formula unless you tell it not to. There are two major approaches used to
replicate formulas:
1. Using Copy and Paste.
2. Using Autofill.

Activity 2.6: Copying Formulas Using Copy and Paste

In this Activity we will use the worksheet in Fig 1.4.
.
 Clear any formula in the worksheet.
 Go to cell B10 and type and enter the following formula: =SUM(B5.B8).
 Now we want to copy this formula to the other columns. Click cell B10. Issue the Copy command.
 Take the mouse pointer to cell C10. Hold down the left button of the mouse and drag right to cell G10.
Row 10 between columns C and G is highlighted.
 Issue the Paste command. Excel has imitated what you did in cell B10 to the other cells.
Now visit all totals shown in column C to G and satisfy yourself that the additions are done correctly. That is,
in cell C10 you will have the formula =SUM(C5:C8) and not =SUM(B5:B8) which you initially copied.

@ K V Mgaya University of Botswana

 Chapter Two 5
Using Excel for Business Applications

Copying Using AutoFill (The Fill Handle)

This is the fastest method to copy one cell or group of cells to an adjacent cell or group of cells. This method
uses the Fill Handle. A Fill Handle is a dark rectangle found at the bottom right corner of a selected cell or
cell range (See Fig 2.3)

Activity 2.7: Copying Using AutoFill

In this Activity we will use the data from Fig. 1.4.
 Click cell B10, and enter a formula =SUM(B5:B8).
 Click cell B10. Use your mouse to point to the bottom Fig. 2.3: The Fill Handle
right corner of the cell. When the cursor becomes a small
black cross, press and hold the left button and drag right up
to cell G10 (See. Fig 2.4)
 Lift you finger and you have copied the formula in cell
B10 to cell range C10:G10.

Using Autofill to Extend Series The square at the

Autofill provides a delightful way to automatically fill in corner is the Fill
several types of series by selecting cells and dragging the Fill Handle
Handle. Autofill can also be used to predict growth trends.

Let us see how Autofill can be use used to automatically fill Selected cell
certain types of series based on values on only one cell. If, for
example, you wanted to enter days of the week in cells A1:G1 starting with Monday you can do the following:
 Type and enter Monday in cell A1
 Select cell A1. Use your mouse to point to the bottom right corner of the cell. When the cursor turns into a
small black cross press and hold down the left button and drag right up to cell G1.
 Release the finger. Cells A1:G1 will be filled with the days of the week, Monday to Sunday.
Several other series can be produced based on values in a single cell.

Fig. 2.4: Copying Using Autofill

@ K V Mgaya University of Botswana

 Chapter Two 6
Using Excel for Business Applications

Activity 2.8: Producing Series Using AutoFill

Fig. 2.5: Data for
 Start a new worksheet and enter the following values: Copying using Autofill Mon, Monday,
January, Q1, Quarter1 1st Period, 1St Semester and One as shown in
Fig 2.3
 Highlight cell range A1:A9
 Use the mouse to point to the bottom right corner of the cell. When the
cursor becomes a small black cross press and hold down the left
button and drag right to cell G9.
 Release the finger.

Except for the entry in cell A9, Autofill has automatically produced series
in columns B to G for each of the entries in column A. The entry in cell A9
was included to show that not every entry can be extended to produce a
series.

Fig. 2.6: Producing Series in Column A to Columns B to G

Extending Values Entered in More Than One Cell

Autofill can be used to create series by extending values entered in two or more cells.

Activity 2.9: Extending Series Entered in More Than One Cell

Start a new worksheet and enter the values shown to the right. Fig. 2.7: Extending Values
Highlight cells A1:B5 from More than One Cell
Use the mouse to point to the bottom right corner of the rectangle.
When the cursor becomes a small black cross press and holds down the
left button and drag right to cell G5.
Release the finger. Autofill will automatically produce series in
columns C to G based on the entries in columns A and B.

@ K V Mgaya University of Botswana

 Chapter Two 7
Using Excel for Business Applications

Fig. 2.8: Extending Series Based on One, Two and Three values

Note A

Note B

Note C

Note A
The shaded entries in cell range A1:A7 were dragged right to extend the series to cell range B1:F7.

Note B
The shaded entries in cell range A9:B15 were dragged right to extend the two column series to cell range
C9:F15.

Note C
The shaded entries in cell range A17:C19 were dragged right to extend the three column series to cell range
D17:F19. Note that the extended series in cell range D18:F18 and D19:F19 are based on the best fit trend.

You can use Autofill to produce series based on entries in more than two columns. Fig. 2.8 shows some more
series that you can use with AUTOFILL. When non sequential numbers are involved, EXCEL gives you a
prediction of growth trends such as the last entry in Fig.2.8. There are more than these; you only need to test
whether Excel will be able to work with the series you are trying to introduce or not. For example, test “Period
1” and “Semester 1”

Other Important Mathematical Functions: AVERAGE, ROUND, ROUNDUP,

ROUNDDOWN, MAX, MIN

AVERAGE
Average is a commonly used statistic. We calculate averages such as age averages, income averages and
rainfall averages. You can calculate the average by adding the values and dividing the sum by the number of
items. For example, the average for numbers 1,2,3,4 and 5 can be calculated using the formula =SUM
(1,2,3,4,5)/5 which will give you 3. Alternatively, if the numbers are in cell range C1:C5 you can use the
formula =SUM (C1:C5)/5 to get the same result.

@ K V Mgaya University of Botswana

 Chapter Two 8
Using Excel for Business Applications

Excel provides the AVERAGE function that you can use to calculate averages. For example, the average for
the numbers 1,2,3,4 and 5 can be calculated using the formula =AVERAGE(1,2,3,4,5) which will give you 3.
Alternatively, if the numbers are in cell range C1:C5 you can use the formula =AVERAGE(C1:C5) to get the
same result.

MAX
This function returns the largest value in a set of values. The arguments in the brackets can be actual numbers,
or cell references. For example, if you write and enter the formula =MAX(-1,-5,5,6,-30,90,60) the function
will give the answer 90.

MIN
This function returns the smallest value in a set of values, the opposite of MAX. The arguments in the
brackets can be actual numbers, or cell references. For example, if you write and enter the formula =MIN(-1,-
5,5,6,-30,90,60), the function will give the answer-30.

ROUND, ROUNDUP AND ROUNDDOWN

The result of dividing two numbers may be a number with more decimal digits than you would like to have.
With Excel you can round the result to the number of digits you wish. In this section, we will discuss how the
Excel functions ROUND, ROUNDUP and ROUNDDOWN can be used.

The syntax of all these functions is:

= function_name(number, num_digits)

where

function_name is the name of the function such as ROUND, ROUNDUP, ROUNDDOWN

number is any real number. It can be a number supplied by you or calculated by a formula.

num_digits is the number of digits you want to round or truncate. num_digits can assume
negative, zero or positive values.

Examples: =ROUND(123.456712, 1) and =ROUNDUP(SUM(C1:C7),3)).

ROUND
The Excel function ROUND is used to round a number to a specified number of digits. Please note the
following when using the ROUND function:
 If num_digits is 0 the function rounds the number to the nearest whole number, such as 93
 If num_digits is 1 the function rounds the number to nearest one decimal place, such as 93.1 whereas if
num_digits is –1 it rounds the number to the nearest tens, such as 90
 If num_digits is 2 the function rounds the number to two decimal places. But if num_digits is –2 it rounds
the number to the nearest hundreds.
 And so on.

ROUNDUP
This function is used to round up numbers. See Fig. 2.9.

ROUNDDOWN

@ K V Mgaya University of Botswana

 Chapter Two 9
Using Excel for Business Applications

This function is used to round down numbers. See Fig. 2.9.

Figure 2.9 Results of Using Different Methods Rounding

Activity 2.10: Using ROUND, ROUNDUP and ROUNDDOWN

In this Activity we will explore the three methods of rounding data: ROUND, ROUNDDOWN and
ROUNDUP.
In cell A2 enter: Num.
In cell B2 enter: num_digits.
In cell C1 enter: Results of Using Different Methods of Rounding
In cell C2 enter: ROUND
In cell D2 enter: ROUNDUP
In cell E2 enter: ROUNDDOWN
In cell A3 enter the number: 1469.464546 and copy it down to cell range A4:A9.
In cell B3 enter the number -3 and in cell B4 enter the number -2. Copy cell range B3:B4 to cell range B5:B9.
In cell C3 enter the formula: =ROUND (A3, B3).
In cell D3 enter the formula ==ROUNDUP (A3, B3).
In cell E3 enter the formula ==ROUNDDOWN (A3, B3).
Copy cell range C3:E3 to cell range C4:E9.
Format cell range C3:E19 correct to three decimal places as shown in Fig. 2.9.

TRUNC
Use this function if you want to throw away the fractional part of a real number. For example, the formula
=TRUNC (9134.99134) will give you the answer [Link] fraction part, 0. 99134 has been thrown away.

ROUND, ROUNDUP, ROUNDDOWN AND TRUNC VERSUS INCREASE DECIMAL AND

DECREASE DECIMAL BUTTONS
Many times we use the Increase Decimal and Decrease Decimal buttons when formatting
numbers. These buttons only increase or decrease the number of decimals shown on a cell. They do not
perform the functions of rounding up or down. That is, they only hide the figures in a decimal although when
you use the decrease decimal button it seems to work like the ROUNDUP function. You may not notice this
until you use the values in calculations.

@ K V Mgaya University of Botswana

 Chapter Two 10
Using Excel for Business Applications

Let us try to understand these differences in the following Activity.

Activity 2.10: Using Roundup() Vs Decreasing Decimal

In a blank worksheet enter the headings A1:E1 as shown in Fig 2.10
In cell A2 type and enter the number 560.475.
In Cell B2 type and enter =ROUNDUP(A2,1).
In cell C2 type and enter =A2
Click cell C2 and then click the Decrease Decimal button until you get only one decimal point as shown
in cell C2 in Fig. 2.10.
In cell D2 type and enter the formula =B2*7.
In cell E2 type and enter the formula = C2*7.
Format the numbers in cell range B2:E2 correct t to two decimal places using decrease decimal button.

Notice how you get different values after subjecting the same number through the ROUNDUP
function and Decrease Decimal.

Fig 2.10: Results of Using ROUNDUP and Decrease Decimal

Note that the contents of cell D2

and E2 are different

The #error Messages

Just as you can make errors in entering numbers and text in a worksheet you can also make errors when
writing formulas. Some formulas will give you results although the results may be wrong because you are
using a wrong formula or referring to wrong cell ranges. Such errors may be difficult to detect
immediately.

Excel helps in cases where a formula cannot properly evaluate a result by displaying an error message.
Fig. 2.11 shows the error messages and what causes them.

@ K V Mgaya University of Botswana

 Chapter Two 11
Using Excel for Business Applications

Error Explanation
Message
######## This error is not necessarily generated by a formula. Reasons for this error include:
1. The number stored is simply too long for the cell width.
2. Another possible cause is a negative date or time
3. The formula produces a big number that cannot fit in the cell width
#NULL! The #NULL! error is raised if reference is made in a worksheet formula to the intersection
of two ranges which do not intersect at all(i.e. do not have common cells). For example, the
cell ranges A1:C4 and D5:F8 do not intersect while cell ranges A1:C4 and C4:E7 intersect
#DIV/0! The #DIV/0! error value occurs when a formula divides by 0 (zero). Any number divided
by zero should generate infinity, a number that is too big even for a computer.
#VALUE! The #VALUE! error value occurs when the wrong type of argument or operand is used, or
if the Formula AutoCorrect feature cannot correct the formula. For example, if you
attempt to divide a text value by a number you will get this type of error. To test this enter
the text “YEAR” in cell A1 and in cell A4 enter the formula A1/5.
#REF! The #REF! error value occurs when a cell reference is not valid. This normally happens
when cells being referred to in the formula have been deleted.
#NAME? The #NAME? error value occurs when Microsoft Excel doesn't recognize text in a
formula. This commonly occurs with mistyped functions such as =SUN(A1:A12) instead
of SUM(A1:A12)
#NUM! The #NUM! error value occurs when a problem occurs with a number in a formula or
function. For example, you will get this error if you try to get a square root of a negative
number or if you use Excel to compute a big number such as 10001000
#N/A The #N/A error value occurs when a value is not available to a function or a formula. If
certain cells on your worksheet will contain data that is not yet available, enter #N/A in
those cells. Formulas that refer to those cells will then return #N/A instead of attempting
to calculate a value.

@ K V Mgaya University of Botswana