MGSLG ICTCFT. (2017). Creating formulas (CC BY-SA)

Transcript

    1. In order to write more intricate formulas, you must use the /,*,-,+ signs on the numeric keypad (right hand side of the keyboard) to divide, multiply, subtract or add respectively.
    2. In your edit field in the panel above your workbook, you can design powerful and unique formulas. Place your cursor in the cell where you want the answer to appear. Activate the edit field by clicking the mouse in the edit space. A red Ω and a green γ appear on the left of the edit field when you have successfully activated it.
    3. Insert an = sign to signal to the program that what appears next is a formula. In the example below column F has been used to work out a term mark. Columns C and D need to be added together and then added to Column E. First, however, this oral mark needs to be converted to a figure out of 50 not 10.
    4. Place the cursor in F4
    5. So use the formula: =(E4*5)+C4+D4 This says that first we must multiply the value in E4 by five and then add it to C4 and D4. This provides us with the Raw Term Mark out of 150 for the first pupil.
    6. We now need to copy this formula to the other class members as we don’t want to write it out again. Select the cell with the valid formula. In the bottom right hand corner of the cell is a small black square. Click and drag the cursor down the column and the formula will be copied to each cell.
    7. Column G has been used to convert the raw term mark figure to a percentage for the reports. The value in F4 is out of 150 so in cell G4 type the formula =F4/150*100. This formula means divide the value in cell F4 by 150 and multiply it by 100. Copy and paste this formula to all the other pupils in Column G.
    8. You can now sum and average these two new columns. The average of column G is the class average.
      It is possible to leave out step 7 above if you make your formula more adventurous in step 5. Instead of writing =(E4*5)+C4+D4 you could have written =((E4*5)+C4+D4)/150*100 which is really a combination of both steps. This new answer that now appears in Column F is the report mark out of 100. Obviously you can adapt formulas to suit your situation.


Last modified: Tuesday, 3 November 2020, 3:27 PM