6
Further steps in spreadsheets

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.

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:

  1. 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.
  2. Click in the formula line to place the caret appear there. You can also do this by pressing the F2 function key.
  3. Click the Function selector button on the toolbar.
  4. Choose Quick... from the function selector menu by clicking on it. You will see a short list of functions.
  5. Double-click on the avg(list) entry in this list. It will appear on the formula line.
  6. 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.
  7. 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.
  8. Your formula is now complete. Click the Tick 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:

  1. Click in cell G3.
  2. Click in the formula line to place the caret appear there or press F2.
  3. Delete the current formula.
  4. Type in the correct formula:
    avg(b3e3)
  5. Click the Tick 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:

  1. Select the range of cells from cell G3 down to cell G18.
  2. 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.

  1. To start writing the formula containing the if() function, click on the cell H3 and then place the caret the formula line either by clicking in it or pressing F2.
  2. Click the Function selector button, then choose Quick... from the menu which appears.
  3. 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.

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.

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:

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.

Evaluating the formula

To evaluate the formula:

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.

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.

  1. Click on cell B23, and click on the formula line to place the caret there.
  2. Click the Function selector button on the toolbar, and then open the Categories submenu. Choose the Database category, then locate and double-click on the dcounta(range,condition) entry in the list of database functions presented.
  3. 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.
  4. 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")
  5. Click the Tick button on the toolbar or press Return↵. The answer 2.00 should appear in cell B23.
  6. Select the range of cells from cell B23 down to cell B25 and click the Fill down button on the toolbar.
  7. 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 /.

  1. Click on cell C23, and place the caret in the formula line.
  2. Click on cell B23. You will see B23 appear in the formula line as if you had typed it.
  3. 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.
  4. Click the Tick button on the toolbar or press Return↵. The result 0.13 will appear in the cell.
  5. Select the range of cells from cell C23 down to cell C25 and click the Fill down button on the toolbar to calculate the remaining fractions.

The second stage is to convert the fractions displayed into percentages.

  1. With the cells still selected, click the Style button on the toolbar.
  2. 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.

  1. 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:

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.