Excel 2007

Introduction to Formulae and Functions

Page 1 of 19
 Contents
Creating Simple Formulae ................................................................................................ 4
Some common formulae 4
The order of precedence 5
Editing a formula 5
Copying formulae 6
Functions............................................................................................................................ 8
Functions and arguments 8
The Sum function 8
Other Functions 10
Using the Formulas Tab 10
View Formulas 11
Function Box 11
Calculation with dates 13
Precision formatting 15
Statistical & mathematical functions ............................................................................. 16
Cell references 16
Date Formulae and Functions ........................................................................................ 17
Absolute cell referencing ................................................................................................ 18
Making a reference absolute 18

Page 2 of 19
Introduction
This workbook has been prepared to help you use Excel to do calculations using basic
Excel formulae and functions. It is aimed at those who have a good understanding of the
basic use of Excel for entering data. It assumes knowledge of moving around a worksheet,
formatting cells, and controlling worksheet display and printing.

Page 3 of 19
Creating Simple Formulae
Formulae allow the calculation of data or values. These calculations range from simple
arithmetic (addition, multiplication etc.) to more complex statistical, logical and database
functions.
You enter a formula by typing it in the cell where you want its result to appear. When you
confirm entry of a formula, Excel will display the result on the worksheet, but the underlying
calculation appears in the formula bar.
Formulae always start with an = (equals) sign.
Place the formula in the cell where the result is to be
displayed.
Formulae should refer to the cell address not the contents
of the cells,
i.e. to add the two numbers shown above the correct
formula is:
=A1+B1
not
=10+15
 The result is displayed in the cell when the Tick button
on the formula bar is clicked, or the Return key is
pressed.
 You can cancel a formula if necessary by clicking on the X button on the formula bar or
pressing the Esc key.
 When the contents of a cell referred to in a formula change, the formula automatically
calculates and displays the new result.
I.e. if the value in cell A1 is changed to 15 in the example above, the formula automatically
recalculates to display the result 30.

Some common formulae

Operator Description Excel Formula

+ Addition =A1+A2 add A1 and A2

- Subtraction =A1-A2 subtract A2 from A1

* Multiplication =A1*A2 multiply A1 by A2

/ Division =A1/A2 divide A1 by A2

^ Exponential =A1^A2 raise A1 to the power A2

% Percentage =A1 % express A1 as a percentage

Page 4 of 19
 =A1*10% returns 10% of A1

These operations can also be combined together. For example:

=A1-A2/(A1+A2)
or
=(A1+B2–D4)*50
Use brackets to ensure that the different parts of the formula are calculated in the correct
order. For example: =(3+2)*4 is not the same as =3+2*4.

The order of precedence

Excel evaluates operators following the conventional rules – it will apply the calculations in a
formula in the following order:
BODMAS: Brackets Of Division Multiplication Addition Subtraction
() brackets first
^ power of
/ and * division and multiplication
+ and - addition and subtraction

Formula Result Take care to observe these rules when creating your own
=3+2*4 11 formulae.
=(3+2)*4 20 Incorrect syntax will result in error.

Editing a formula
1. Double-click on the cell containing the formula. The cell will switch from displaying the
result of the formula to the formula itself.
2. Click the mouse over the part of the formula you wish to change to insert the cursor
there. Type any new character or use the Backspace or Delete keys to remove
characters.
3. Press Enter to confirm your changes, or Esc to exit the cell without saving your
changes.
or
1. Move to the cell containing the formula you wish to change.
2. The formula will be displayed in the formula bar.
3. Click into the formula bar and make the necessary changes.
4. Click on the tick to the left of the formula to confirm your change, or the cross to close
the formula without saving your changes.
or
1. Move to the cell containing the formula you wish to change and press the F2 key.

Page 5 of 19
2. Use the arrow keys to move the cursor to the edit position. Make your changes and exit
the cell as explained above.

Copying formulae
Formulae can be copied using the Copy and Paste buttons in the same way as data can be
copied in a worksheet.
1. Select the cell containing the formula to be copied.
2. From the Home tab, click on the Copy icon
3. Move the cursor to the new location.
4. From the Home tab, click on the Paste icon
The formula will be copied relatively, ie the cell references will change to the relative position
to where they are copied. For example, if you had a formula in C1 which contained the
formula A1+B1, and then copied the formula to C2 and C3, then the formula in C2 would
read A2+B2, in C3 would be A3+B3, and so on.
This is because the formula in C1 is adding the 2 cells to the left, and will do so wherever
you copy the formula to – it will always add the 2 cells to the left of it.

Using the fill handle

As formulae are copied to adjacent cells most of the time, it is much quicker and easier to
use the “Fill Handle” to copy one formula to many cells.
1. Move to the cell that has the formula that you want to fill.
2. Position your mouse pointer over the fill handle. It will change to a black plus.
3. Drag the black plus down or right over the cells where you want your copied formula to
generate results. You will see an outline around those cells.
4. Release the mouse when the outline includes all the cells where you want results.

Page 6 of 19
Using keystrokes
You can fill a column or a row of formulae using the keyboard.
1. Select the cell containing the formula to fill and the cells where you want to copy to:

2. Press Ctrl+D to fill down.

or
Press Ctrl+R to fill right.
There are no keystrokes to fill up or left. Instead, repeat step one above and then click Edit
on the menu bar, choose Fill and select the direction for the fill from the resulting sub-menu.
The Fill Handle can also be used to fill up and left.

Page 7 of 19
Functions
As well as performing your own arithmetic operations on values in a worksheet. Excel also
provides many built-in functions which automate a number of types of calculation. Functions
are pre-programmed formulae – you are probably already familiar with the use of functions
on a calculator (for example, the square-root function, trigonometric functions, logarithms
etc.). Excel has more than 300 functions covering a range of statistical, mathematical,
financial and logical operations. If you have many numbers in a group of cells that you wish
to combine in a formula, typing the formula becomes laborious. Using a function offers a
shortcut method.
Examples of the most commonly used functions include the Average function, which
calculates the mean of a group of cell values, the Sum function, which adds together a
group of cell values, and the Min and Max functions, which determine the lowest and highest
values in a group of cells.

Functions and arguments

Functions are usually written with the equals sign (=) followed by the function name and
then parentheses containing the argument. Usually the argument just contains the range of
cells which the function will operate on. For example, the Average function is written as:
= AVERAGE(A1:A4)
function
name argument

The argument of a function is placed in brackets. To specify a range of cells a colon is used
between the first and the last cell address. For example, (A1:A4) will specify cells A1, A2, A3
and A4.

The Sum function

The Sum function is a useful function and popular. It simply adds together a range of cell
values. The formula:
=A1+A2+A3+A4+A5+A6+A7+A8
can be replaced by:
=SUM(A1:A8)
This adds up the contents of the cells A1 to A8.
 The function can be typed at the keyboard like any other formula.
 The function can be created using the Insert Function dialog box by selecting the
Formulas tab and clicking on the Insert Function button
 The SUM function can be created using the AutoSum button (this is the easiest method).

Page 8 of 19
AutoSum
The AutoSum feature allows you to add all numbers in a contiguous row or column.
To use AutoSum:
1. Click a cell below the column of numbers or to the right of the row of numbers.

2. Click the AutoSum button on the Home tab. The same button also appears
on the Formulas tab.

3. Excel automatically guesses the range of cell references that you wish to sum (these can
be amended if necessary).
4. If Excel doesn’t select the correct range, highlight the cells that want to calculate with the
mouse.
5. Confirm the formula entry by clicking on the tick (to left of the formula bar).
Note: Always check automatically generated formulae before accepting them, as Excel
doesn’t always guess correctly.
You can add several ranges, or cells, together by pressing the comma to separate them. For
example:
=Sum(B4:B6,B9,C8:C12,D5) Adds up cells B4,B5,B6,B9,C8,C9,C10,C11,C12 and D5

Page 9 of 19
Other Functions
Clicking on the arrow at the side of the AutoSum button, will give you a list of other popular
functions: Average, Count Numbers, Max, Min. They are entered in exactly the same way
as the Sum function.

Using the Formulas Tab

If you know the name of a function, you can simply type it in together with the argument or
range of cells you want to apply it to. However, an easy way to work with functions is to use
the Formulas tab. (The AutoSum button here is the same as the one on the Home tab.)

1. Position the cursor in the cell which is to contain the result, and click on the on the
Formulas tab.
2. The Insert Function dialog box is displayed:

3. Using the Search for a function box, you can type a description of what you want to do.
The Most Recently Used category often offers the most likely choices. Select an
appropriate category. The functions in that category are shown in the lower half of the
window.

Page 10 of 19
If in this example we choose AVERAGE and click on OK, the Function Arguments dialog
box will display as shown below. It may well obscure the part of the worksheet you want to
work on. However it can be moved simply by clicking and dragging anywhere in the grey
shaded box. It can also be shrunk by clicking on the Shrink/Enlarge buttons.

Note that Insert Function guesses the range of cells to be used in the calculation (B4:B10 in
the example). Click OK if this is correct. Alternatively type the range in, or highlight the cells
required in the worksheet.
Notice that a moving border appears around the specified cells as the range is entered in
the dialog box. Click OK.

You can view the completed formula by clicking in the cell, and looking at the contents of the
Formula bar.

View Formulas
It can be very useful to view the formulas on the worksheet rather than the results. You can
then print them off or just check that they are correct.

On the Formulas tab click on the Show Formulas button . This is

a toggle button, so click on it again to go back to seeing the results of formulas.

You can also use a quick key combination: You can use the shortcut keyboard keys:
CTRL + ` [Grave Accent Key] to show formulas in Excel. (The ` Grave Accent
key is the one below the Escape key on the far upper left side of the keyboard). This
will toggle, so pressing the same combination will turn of showing formulas.

Function Box
The Function Box groups the most commonly used functions for quick and easy access.
Page 11 of 19
When you become more familiar with functions, you may prefer to type them directly into a
cell. Excel helps you to remember the syntax of the function by displaying a drop-down list
of functions and also the Function Box becomes active:

1. Position the cursor where you want to insert the function. Type in the equals sign and
start typing the function name you want to use into the cell.
2. Excel will present a list of functions beginning with the letters you type in.
3. Excel also displays the Function Box to the left of the Formula bar. Click the drop-down
list arrow to the right of the Function box to display a list of function names.
4. Select the function you require by clicking its name from the list.
5. If your function isn’t listed, click the More Functions option to access the Insert Function
dialog box.
6. Excel will place the chosen function on the worksheet in the selected cell. You can see
the selected function being built on the Formula bar.
With some functions, Excel tries to guess which cells you want included as the function
arguments. Click OK to accept Excel’s guess and confirm the function, or select the correct
cells as described above.

Page 12 of 19
Calculation with dates
Excel also allows you to perform calculations with dates. All dates are stored in Excel as
sequential numbers. By default, January 1 1900 is serial number 1, and January 1, 2004 is
serial number 40933 because it is 40,933 days after January 1, 1900. Excel stores times as
decimal fractions because time is considered a portion of a day.
Because dates and times are values, they can be added, subtracted, and included in other
calculations. You can view a date as a serial value and a time as a decimal fraction by
changing the format of the cell that contains the date or time to General format.
Viewing dates as numbers
To view dates as numbers:
1. Select the cell and click Cells on the Format menu.
2. Click the Number tab, and then click Number in the Category box.

Calculating the difference between two dates

In the following example the date in cell B1 has been subtracted from the date in cell B2.
The result in cell B3 has been formatted to display a number (the number of days between
two dates) with no decimal places.

NB: You will need to format the result of the formula to a number format, as it may display
as a date.
If you want to know what the date is 3 weeks’ time, and you have the current date in cell A1,
then your formula could be:
=A1+21
NB Excel won’t recognise a date just typed in directly into a formula: Eg =12/1/2012+21.
You would have to use a date function to convert the date into one that Excel can
understand as below:

Page 13 of 19
=Date(2012,1,12)+21 The arguments being: (year,month,day)
=Today() Current date – this is a dynamic date (will change every day). You
could use this in a formula to see what the date will be in 3 weeks’
time from today’s date: =Today()+21
=Now() Current time

Page 14 of 19
Precision formatting
Care must be taken when working with formatted numbers. It is important to remember that
formatted numbers, i.e. the numbers which appear on the screen, may not be the same as
the value stored in the cell or the numbers used in calculations. The discrepancy can cause
the results displayed to be different from the manually calculated answers.

In the example opposite, there are two columns of numbers that

appear to be the same. The first column adds up to 95 but the second
column adds up to 100. Take a close look at the value stored in cell
A1, as displayed in the Formula bar. The value stored in all the cells
in the first column is actually 9.5. This has been formatted to appear
as a whole number (integer). The calculation is actually correct
(10*9.5=95), although it appears to be incorrect. The problem can be
avoided by using number formats cautiously, or it can be resolved by
setting the precision for the entire worksheet as explained below.

Setting the precision of number formats

To set the precision of number formats for an entire worksheet:

1. Click the Office button , then select Excel Options button

2. Click on the Advanced category
3. Scroll down to When calculating this workbook
4. Choose the Precision as displayed box and click OK
When you choose OK you are warned that constant numbers throughout the workbook will
be rounded permanently to match cell-formatting.
This will affect all cells in the worksheet. If you only want to round certain cells, then you can
use the =ROUND function to round to a specific number of decimal places or the =INT
function to convert to an integer only.
Example:

The =ROUND function will round the contents of cell C6 to 2

decimal places, using mathematical rounding, and will return
7.57

Page 15 of 19
Statistical & mathematical functions
Some of the most commonly used statistical and mathematical functions are shown below.

Function Example Description

MAX MAX(C1:C10) Finds the largest cell value in the specified range of cells.
MIN MIN(C1:C10) Finds the smallest cell value in the specified range of cells.
AVERAGE AVERAGE(C1:10) Finds the average cell value in the specified range of cells.
MEDIAN MEDIAN(C1:C10) Finds the median or middle value in the specified range of
cells.
STDEV STDEV(C1:C10) Finds the standard deviation of the values in a range of cells.
COUNT COUNT(C1:C10) Counts the number of cells containing numbers.
COUNTA COUNTA(C1:C10) Counts the number of cells containing numbers or letters (i.e.
the number of non-blank cells).
COUNTBLANK COUNTBLANK(C1:C10) Counts the number of blank cells.
COUNTIF COUNTIF(
INT INT(C6) Returns the integer value (5.35 becomes 5, 7.34 becomes 7)
ROUND ROUND(C1, 2) Rounds the cell value to the specified number of decimal
places (2 in this example; use 0 to get a whole number).
SQRT SQRT(C1) Calculates the square root of a cell value.
RADIANS RADIANS(C1) Converts angles from degrees to radians.
SIN SIN(C1) Calculates the Sine of an angle (in radians – use the
RADIANS function to convert degrees into radians).
Other trigonometric functions include COS and TAN.

Cell references
In functions, you often need to refer to a range of cells. The way Excel displays cell
references in functions depends on whether the cells you want the function to act upon are
together in a block, or in several non-adjacent cells or blocks.
The table below explains how to use different operators to refer to cells:

Operator Example Description

Reference B5:B15 Range operator that produces one reference to all the
operator : (colon) cells between two references, including the two
references.

, (comma) SUM(B5:B15,D5:D1 Union operator that combines multiple references into one
5) reference.

(single space) =B5:B15 A7:D7 Intersection operator that produces one reference to cells
common to two references. In this example, cell B7 is
common to both ranges; therefore the result would be the
contents of cell B7.

Page 16 of 19
Date Formulae and Functions
Dates are treated as numbers in Excel, therefore calculations can be performed just as with
other values. Excel has a 200 year calendar and each day of the year is given a serial
number, starting with 1/1/1900 which is day 1, then 2/1/1900 is day 2 and so on.
If you want to know what the date is 3 weeks’ time, and you have the current date in cell A1,
then your formula could be:
=A1+21
NB Excel won’t recognise a date just typed in directly into a formula. Eg =12/1/2012+21.
You would have to use a date function to convert the date into one that Excel can
understand as below:

=Date(2012,1,12)+21 The arguments being: (year,month,day)

=Today() Current date – this is a dynamic date (will change
every day)
=Now Current time

Page 17 of 19
Absolute cell referencing
The ability to copy formulae from one location to another in a spreadsheet can save you a
significant amount of work. Normally, if you copy a formula involving a cell reference to
another location, the cell reference is adjusted relative to its starting point. So, for example,
if you copy a formula which multiplies two cells to the left of it, the formula will adjust to the
new location by multipling the two cells to the left at the new position. Ie if you start a
formula is in C1 and multiply A1 by B1, and you then copy the formula to C2, the formula will
become A2 multiplied by B2, as these are the two cells to the left of C2. The formula has
updated automatically to refer to adjacent cells. This is an example of a relative
referencing system.
Sometimes we may need to refer to a specific cell location in a worksheet, and so we want
that cell reference to remain unchanged, regardless of where the formula is placed. We
need a method to fix our cell reference so that it does not update when we copy the formula
to another location – we need an absolute cell reference.

Making a reference absolute

If you type a $ sign before both the column letter and the row number of the cell reference
then the relative reference A1 becomes the absolute reference $A$1. This absolute
reference won’t change if you copy the formula.
A beter method is the keyboard shortcut, F4. This is quicker and more accurate.
When constructing a formula and the cell which you want to make absolute is selected,
press the F4 key.
$ signs are automatically placed in front of the column and row references.

Absolute references explained

The data on the left show the basic earnings for a
group of staff. Their manager has decided to
award them a bonus payment, and wishes to store
the total pay in column C.
The formula for cell C6 is =B6+$B$3. Here the $ is
used to make the reference to cell B3 absolute.
When this formula is copied into cells C7:C12, the
formula updates as shown.
Cell B6 in the initial formula is relative, therefore
this reference will change when copied, but B3 is
absolute and will not change to another reference
when copied.

Page 18 of 19
Mixed references
If only the column reference or the row reference is to be absolute, prefix one or other of
these with a $ sign. For example, if the column is to be absolute and the row relative A1
becomes $A1, if the row is to be absolute and the column relative A1 becomes A$1.
1. Double-click in the cell as if to edit it.
2. Highlight the cell reference to be made absolute and press F4. Note that by pressing F4
a number of times you cycle through different options for creating a mixed reference.

A1 Relative
$A$1 Absolute
$A1 Mixed (Column Absolute, Row Relative)
A$1 Mixed (Column Relative, Row

Page 19 of 19