Spreadsheets - Formulas
Use basic arithmetic and logical formulas in a spreadsheet addition, subtraction, multiplication, division.
To enter formulas into the worksheet cell · Place the cursor in
the cell where the formula will appear.· Enter an = (equal) sign.·
Enter the expression that will produce the result you want. This can consist
of operands, values, variables, and symbols which represent mathematical
procedures such as A5+E5.
You can use the following symbols: -
+ to add
- to subtract
* to multiple
/ to divide
· When the formula is complete, press Enter. The result of the formula will be calculated and displayed in the cell.· You can display the formula itself in the Formula bar
at the top of the screen by placing the cell pointer on the cell.· If there is an error in a formula, an error message is displayed which will begin with a # sign. · NOTE: If you cannot find an error in a function you can use the Paste Function to debug it. Highlight the problem cell and click on the Paste Function icon on the formula bar or Standard toolbar.
Recognize standard error messages associated with formulas.
Common formula error messages When writing formulas
it is easy to make a mistake, listed below are some common error messages.
Message Meaning
##### The contents of the cell cannot be displayed correctly as the column
is to narrow.
#REF! Indicates that a cell reference is invalid. This is often displayed
when you delete cells that are involved in a formula.
#NAME? Excel does not recognize text contained within a formula.
On-line Help with formula error messages You can use the on-line Help
to get further information about errors within formulas and the meaning
of the error messages.· Click on the Microsoft Excel Help icon.
· When the Office Assistant is displayed, enter the word error,
and then click on the Search button.
· Select "Trouble shoot formula and error values".
You will then see the following displayed. From this screen you can get
detailed information about each type of error message.
Use the autofill tool/copy handle tool to copy or increment data entries -below.
To use AutoFill · Enter a starting value
for the series that you wish to create. · Enter the second value
in the next cell.· Move the mouse pointer to the "fill handle"
(this is the small black square at the bottom, right of the selected area).
When the mouse pointer is over the fill handle, it will change shape,
from a large white cross to a small black cross.· Depress the mouse
button and drag as far as you wish to extend the range. · When
you release the mouse button the range will have been filled with incremental
values.Autofill can be used for number sequencing, days of the week, or
months of the year.
Before using Autofill After using Autofill
To see what AutoFill options are available · Click on the Tools drop down menu and select the Options command. From the dialog box displayed select the Custom Lists tab. You will see a number of pre-lined lists displayed here. NOTE: You could add your own custom lists using this dialog box.
Understand and use relative cell referencing in formulas or functions.
What is relative addressing? By default Excel uses relative addressing.
This means that when you use a formula the components in the formula are
relative. What does this mean? Consider the following example: The VAT
(Value Added Tax) rate is contained in cell 1E.In cell B6 is the price
of a laser printer, in this case £500In cell C6 is the formula =B6*E1%In
cell D6 is the formula =B6+C6If we used drag and drop techniques to highlight
cells C6 and D6 and extend the formulas down the page, we might expect
this to work OK but it does not. · To try this click on cells C6
and while keeping the Control key depressed click on cell D6. Release
the Control key and the two cells will remain selected.· Move the
mouse pointer to the fill handle, i.e. the small, square black dot at
the bottom right corner of the selected range.· Depress the mouse
button and drag down for two rows, then release the mouse button.What
you see is the following. If you look in cells C7 and C8 there is no VAT
calculated! Clicking on cell C7 gives us the clue as to why this did not
work. It contains a formula as follows:=B7*E2%
I.e. instead of picking up the VAT rate from cell
E1, the formula is pointing to E2 (which is blank). This illustrated relative
referencing, the referencing is using an X (i.e. right-left) and Y (i.e.
up-down) set of co-ordinates rather that an absolute addressing system,
that would always point to the contents of a particular cell.
Understand and use absolute cell referencing in formulas or functions.
What is absolute addressing? Following on from
the previous example, were relative reference was used we can fix the
problem by using an absolute address. Remember that we had a VAT percentage
amount in a cell, and when this was referenced in a relative manor while
dragging and dropping formulas, then the formula no longer worked.Again
we will start with the following: The VAT rate is contained in cell 1E.In
cell B6 is the price of a laser printer, in this case £500In cell
C6 is the formula =B6*$E$1%In cell D6 is the formula =B6+C6If we used
drag and drop techniques to highlight cells C6 and D6 and extend the formulas
down the page, we might expect this to work OK and this time it does!·
To try this click on cells C6 and while keeping the Control key depressed
click on cell D6. Release the Control key and the two cells will remain
selected.· Move the mouse pointer to the fill handle, i.e. the
small, square black dot at the bottom right corner of the selected range.·
Depress the mouse button and drag down for two rows, then release the
mouse button.As you can see to use absolute addressing, you prefix both
parts of the cell address, by a dollar ($) symbol.While entering formula
into a worksheet your can use the F4 key to quickly convert a relative
cell reference to absolute, e.g. Select a cell and type =B4 and press
the F4 key, Excel will insert the dollar symbols for you.
Working with Functions
Use the sum function.
To sum numbers automatically · Select the cell you want to contain the sum formula.· Click the AutoSum icon on the Standard toolbar. Excel will create a sum formula, using with the range it thinks you want to sum.· If the range is correct, press Enter. If it is incorrect, select the range you want to sum and press Enter.
To use the SUM function · Select the cell where you want to place the formula.· Type =SUM(· Highlight the range you wish to sum.· Press Enter.
Common Functions Function Name Use
AVERAGE Used to determine the average number of the selected cells.
COLUMNS Used to return the number of columns within a reference.
COUNT Used to count how many numbers are in the list of arguments.
MAX Used to return the maximum number from a list of arguments.
MIN Used to return the minimum number from a list of arguments.
ROUND Used to round off numbers to a specified number of decimal points.
SUM Used to add the contents of selected cells.
Commonly used functions, as displayed within the Paste Function
Use the average function.
To use the AVERAGE function · Select the cell where you want to place the formula.· Type =AVERAGE(· Highlight the range of which you wish to calculate the average.· Press Enter.