7 Writing Formulae
This chapter introduces formulae and shows you how to write them. It
introduces the operators and functions which you need to use in formulae and
shows you the ways Fireworkz can help you to write formulae quickly and easily.
It includes sections on:
- The function selector
- The if() function
- Analysis functions
- Arithmetic operators
Using functions
Load the spreadsheet exams from the Tutorial directory.
You will see a list of class members and their marks in four exams.
You need to calculate the average mark for each pupil, and to award them
each a single grade based on this average mark. You then need to work out the
number of pupils receiving each grade, and the percentage of the class
receiving each grade.
The function selector
In the previous chapter you added columns of numbers using the
Autosum button.
You saw that this created a formula which you could read on the formula line.
In this chapter, you will be entering formulae directly on the formula line.
Note that unlike text and numbers, you cannot enter a formula directly into a
cell - you have to use the formula line.
Working out an average
The first step is to work out the average number of marks earned by each
student:
-
Click in cell G3. You need to add the avg() function to
this cell. You can type in functions directly, but an easier method is to use
the function selector.
-
Click in the formula line to place the caret appear there. You can also do
this by pressing the
F2 function key.
-
Click the
Function selector tool button on the toolbar.
-
Choose
from the function selector
menu by clicking on it. You will see a short list of functions.
-
Double-click on the avg(list) entry in this list.
It will appear on the formula line.
-
The word list is called the
parameter to the function.
You need to replace it with the actual data on which the function will operate.
The avg() function needs a list of numbers to operate
on. You could supply a list of individual cell references, but in this case you
will supply a range, indicating that the function should operate on the data
in all the cells contained between, and including, the two cell references
given.
You could enter the range details by typing in the top left and bottom right cell referencess in the range, but it is easier to use the mouse.
-
Select the range of cells starting at cell B3 and ending at cell E3.
You will see these cell references appear between the brackets in the formula
on the formula line, replacing
list.
-
Your formula is now complete. Click the
Tick tool button on the toolbar or press
Return↵ to enter the formula. You will see the result 76.75 appear in the cell.
If you make a mistake
If you make a mistake and the correct result does not appear, then you need
to change the formula:
- Click in cell G3.
-
Click in the formula line to place the caret appear there or press
F2.
- Delete the current formula.
-
Type in the correct formula:
avg(b3e3)
-
Click the
Tick tool button on the toolbar or press
Return↵ to enter the corrected formula.
Repeating a calculation
You need to perform the same calculation for all the students in the class.
This can be done using one of the
Fill buttons.
When you use a
Fill button to copy a formula,
it updates the formula in each column or row, so that the data it operates on
has the same relative position to each cell containing the formula.
So when our formula appears in row 4, it will calculate the average value in
row 4, and so on:
- Select the range of cells from cell G3 down to cell G18.
-
Now click the
Fill down button (because you are
filling down a column). The column will fill with the average mark for each
student.
You can check the formulae which have been created by clicking on any of the
cells in column G and looking at the formula which appears in the formula line.
The if() function
The next stage is to assign a grade to each student, based on their average
mark. The school operates the following formula:
Average mark |
Grade |
over 75 |
A |
50 to 75 |
B |
under 50 |
C |
You could simply look at each mark and type in the appropriate grade.
However, the process can be simply automated by writing a formula which
calculates the grade, using the if() function.
The aim of our formula is to look at a mark and initially test whether it is
over 75. If it is, then the letter A will be printed in the column.
If not, the formula will test whether if the mark is between 50 and 75.
If it is then the letter B will be printed.
If not (i.e. it is less than 50), then the letter C will be printed.
The steps required to achieve this are spread over the next few sections.
-
To start writing the formula containing the if() function,
click on cell H3 and then place the caret the formula line either by
clicking in it or pressing
F2.
-
Click the
Function selector button,
then choose
from the menu which appears.
- Double-click on the if(condition,then,else) entry in the list of functions presented to you and it will appear on the formula line.
Writing the condition
The if() function has three parameters.
The first is the condition, i.e. the test which the formula applies to the mark.
If the condition is true, then the action detailed in the second parameter takes place.
If it is false, then the action detailed in the third parameter takes place.
You need to first test that the mark is greater than 75. To do this you simply
enter a comparison as the first parameter:
G3>75
Fireworkz will take the contents of the cell, and test to see if the answer
is true (i.e the contents of cell G3 are greater than 75),
or false (i.e. the contents of cell G3 are less than or equal to 75)
and act accordingly.
-
4.
To enter the cell reference G3, simply point at that cell and
click the left mouse button.
-
5.
Type in the rest of the condition:
>75
The formula will now read:
if(G3>75,then,else)
Printing a string
Next you need to detail what will happen if the condition is true.
You want the letter A to appear in the cell, so you simply need to type this in.
Text manipulated by formulae needs to be surrounded by double quotes, so that
Fireworkz can recognise it and process it correctly. It is referred to as a
string because it is a string of characters.
-
6.
Position the caret after the first comma.
As you move on to subsequent parameters, they will be highlighted and
appropriate help will appear in the status line.
As before, just type over the parameters to replace them.
-
7.
Type over the then parameter:
"A"
The formula will now read:
if(G3>75,"A",else)
Note that all parameters must be separated by commas, so that Fireworkz can tell them apart.
Nesting if() functions
The last stage is to enter what happens if the average mark is less than
75. In this case, a further test will be applied to the mark, to test whether
it is 50 or more. To write this part of the formula, you need to use a further
if() function. Placing one function inside another is known
as nesting.
There is no need to go back to the function selector; this time you should
try typing in the function directly:
-
8.
Between the last comma and the closing bracket, type over the
else parameter:
if(,,)
Your formula will now read:
if(G3>75,"A",if(,,))
Make sure that you have the correct number of
brackets at the end of the formula - there should be two closing brackets next
to each other.
The next stage is to enter the new condition, the first parameter to the
second if() function.
This time you will test whether the mark is greater than or equal to 50.
-
9.
Type between the opening bracket and first comma of the second if() function:
G3>=50
There is no need to test that the mark is under 75 as well because you have
already assigned a grade to those marks and this calculation will not be
performed on marks which met the condition in the first part of the function.
Your formula will now read:
if(G3>75,"A",if(G3>=50,,))
The final stage is to enter what will be done if
this second condition is true or false. If it is true (i.e. the mark is between
50 and 75) you want to print the grade B; if it is false, the mark will be
neither over 75 nor 50 or over, so it must be under 50, and receive the grade C.
-
10.
Type "B" for the second
(then) parameter
and "C" for the third
(else) parameter.
The finished function should look like this:
if(G3>75,"A",if(G3>=50,"B","C"))
Evaluating the formula
To evaluate the formula:
-
11.
Click the
Tick tool button on the toolbar or press
Return↵.
You should see the result A appear in the cell containing the formula.
Note that the quotes are not displayed.
If you have made a mistake, then remove the formula as described earlier in this
chapter and either type in the correct formula shown in the previous step or repeat the prodcedure from the start.
Finally, you need to perform the same calculation for each student in the
class. Again, you can use the
Fill down button to do this.
-
12.
Select the range of cells from cell H3 down to cell H18 and click the
Fill down tool button on the toolbar.
The column will fill with the grades for each student.
Analysing information
The next stage is to analyse the information on the grades for the class as a
whole. You need to count the number of students with each of the grades.
The easiest way to do this is to use one of the database spreadsheet
functions. Database spreadsheet functions make a selection from a set of
records and perform a calculation on the selection.
You need to find the students with each grade and simply count how many
there are, so the dcounta() function is the one to use.
- Click on cell B23, and click on the formula line to place the caret there.
-
Click the
Function selector tool button on the toolbar, then choose the
category from the
sub-menu.
Then locate and double-click on the
dcounta(range,condition) entry in
the list of database functions presented.
-
This function has two parameters; the first gives the range of cells on
which the function is to operate and the second gives the condition to be applied
to make the selection. In this case the range of cells are those cells
containing the grades, cells H3 to H18. Type the following as the first
(range) parameter:
H$3H$18
Note that inserting the dollar signs in the
cell reference means that the following part of the cell reference
(here, the row part) will not be changed when the formula is copied using the
Fill down button, but remain
unchanged so as to always refer to the cells containing the student grades.
-
After the comma, type the following as the second
(condition) parameter:
H$3H$18="A"
Note that you need to enter the quote marks
around the text string for Fireworkz to recognise it as such.
The finished formula should look like this:
dcounta(H$3H$18,H$3H$18="A")
-
Click the
Tick tool button on the toolbar or press
Return↵. The answer 2.00 should appear in
cell B23.
-
Select the range of cells from cell B23 down to cell B25 and click the
Fill down tool button on the toolbar.
-
In cell B24 you are looking for students with grade B,
and in cell B25 looking for students with grade C.
Edit the formulae in these cells to contain the correct letter.
The formulae will be identical to that in B23 except that the final text string
(i.e. the grade) will differ.
Using operators
As a finishing touch, you can display the information about the number of students with each grade category as a percentage.
There are two parts to this process. First you need to calculate the
proportion of students with each grade as a fraction, then you need to use a
percentage style to format and display the result correctly.
To calculate the proportion with each grade you simply divide the number of
students with that grade by the total number of students. For simple arithmetic operations such as this, you use the arithmetic operators
+, -, * and /.
- Click on cell C23, and place the caret in the formula line.
- Click on cell B23. You will see B23 appear in the formula line as if you had typed it.
-
You need to divide this number by the total number of students, which you
can work out using the counta() function, so type in:
/counta(A$3A$18)
Note the use of the dollar sign again to ensure that the cell reference does not change when the formula is copied.
-
Click the
Tick tool button on the toolbar or press
Return↵. The result 0.13 will appear in the cell.
-
Select the range of cells from cell C23 down to cell C25 and click the
Fill down tool button on the toolbar
to calculate the remaining fractions.
The second stage is to convert the fractions displayed into percentages.
-
With the cells still selected, click the
Style tool button on the toolbar.
-
Choose
Percentage from the list of styles
and click the
Apply button.
The numbers will be displayed as a percentage. In this case, this means that
they are multiplied by 100, and shown with no decimal places and a percentage sign placed after them.
Updating information
The advantage of using a spreadsheet instead of a pencil and paper is the
ease with which information can be recalculated.
After all the marks have been gathered and the analysis completed, news
comes in that the external moderators have changed the marks for some English
students.
-
Click on cell B3. Change the number it contains to 65.
This changes the average score to under 75, and so changes the grade for that
student, the number of students with each grade and the percentage of students with each grade.
All the calculations will be updated almost instantaneously.
Summary
In this chapter you learned how to write formulae, including the following
stages:
- Using the function selector
- Typing in functions on the formula line
- Using arithmetic and Boolean (logical) operators
- Using the Fill buttons with functions
- Using database spreadsheet functions
The User Guide contains more information on using functions and
operators and writing formulae, as well as full details of each function,
including its parameters.
You can also access information on functions through the function reference
section of the on-line help system.