ICT in het Onderwijs

How do I create a formula in my grade book?

Updated on

Create a formula grade item

With a formula grade item you can assess students based on their score for multiple or different grade items in the grade book. The formula can be used to make Brightspace calculate the final grade based on conditions the students has to fulfil.

For example, you might:

  • give students a 100% for a category if they achieved a 75% for all grade items within that category.
  • drop the two lowest grades out of three.
  • fail students who have received a score below the 60% either on the essay or the exam.


  • Navigate to Administration in the navbar of your course.
  • Click Grades.
  • Click the tab Manage Grades (if you do not go there automatically).
  • Click New and then click Item.
  • Click Formula.
  1. Name the grade item. For Short Name and Show Description add a short name and description if desired. A description can be useful when you need to remember what exactly a formula can do. This is also useful for coworkers in the course.
  2. Enter the maximum amount of points students can acquire under Maximum Points.
  3. Click Edit Using the Formula Editor.

The editor opens in a new window. You can use the editor to build your formula. You do this by using the buttons in the editor; you cannot type yourself.

  1. You will find arrow keys below the editor on the left. You can use this to navigate within the formula (for example when you realise you have made a mistake in the middle of the formula, do not delete the entire formula up until that point, but rather use the arrows to navigate to the piece concerned and then change that). Note that you can also use the arrows on your keyboard!
  2. Use Function to select a function to calculate the grade based on variables:
    • MAX: calculates which variable has the most value. 
    • MIN: calculates which variable has the least value. 
    • SUM: calculates the sum of the variables. 
    • AVG: calculates the average of the variables. 
    • IF: checks whether the variables meet certain conditions and calculates the value based on the meeting of these condtions.
    • NOT: checks whether the conditions have been met for this variable. Calculates the value based on the meeting of all conditions.
  3. Click Start to add the function, Next Term to add the next variable (variables are separated with a comma) or click End to close the function.
  4. Behind Grade Item you select the grade item you want to add to the formula. Then you have to select which value of the grade item must be used:
    • The score the student has achieved (Point Received).
    • The highest score one might achieve for this grade item (Max Points).
    • The percentage of the grade the student has acquired with this grade item (Percent).
  5. Click Insert to add the grade item to the formula.
  6. Click AND or OR, or use the keys on the numpad to add one or multiple values to the formula.
  7. Use Backspace (or the backspace key on your keyboard) to delete the element left of the cursor, or click Clear to delete the entire formula.
  8. On the left top side of the editor you will see two buttons: 
    • Use Validate to check whether the formula is correct. You will receive a confirmation of the formula is correct. If the formula is incorrect, you will get to see which part causes the error as well as the type of error.
    • Use Preview to see the grades the students would receive if you were to use this formula. Preview will only work if the formula is correct.

When you are done, click Insert to add the formula to your grade item.

Example 1

You want Brightspace to calculate a grade based on a number of conditions students have to meet. For example, you have two grade items: an essay that counts for 20%, and an exam that counts for 80%. For both components students have to score a 5.5 or higher, or else they will fail.

The formula will then be as follows:

=IF{ [Essay.Points Received] >= 5.5 AND [Tentamen.Points Received] >= 5.5, [Essay.Points Received] *0.2 + [Tentamen.Points Received] *0.8, 0 }

Explanation

You only want to give the students their credit if both grades are a 5,5 or higher. To achieve this, use IF. The IF works like this:

  1. Behind Function, enter IF and click Start
  2. Enter the variable students have to meet; in this case both grades need to be equal to or higher than a 5,5. Behind IF you will then get [Essay.Points Received] >= 5.5 AND [Tentamen.Points Received] >= 5.5.
  3. enter the next variable: what will happen when IF is true? Separate variables with a comma (click Next Term). When IF is true the score the student received for the essay will count for 20%, and the score for the exam counts for 80%. This means you get [Essay.Points Received] *0.2 + [Tentamen.Points Received] *0.8.
  4. Add another comma (Next Term).
  5. Enter what happens when IF is not true; in this case it will be a 0, because if the students receives a grade below a 5,5 for either the essay or the exam, they will automatically fail.
  6. Add an End when you are finished with the IF.

Example 2

You want Brightspace to drop the two lowest grades when the student has received three grades. This means you have three grade items, out of which the two with the lowest score do not count.

The formula will then look as follows:

= SUM{ [Ass1.PointsReceived], [Ass2.Points Received], [Ass3.Points Received]} - MIN{ [Ass1.PointsReceived], [Ass2.Points Received]} - IF{ MIN{[Ass2.Points Received], [Ass3.Points Received]} = MIN{ [Ass1.PointsReceived], [Ass2.Points Received]}, MIN{ [Ass1.PointsReceived], [Ass3.Points Received]}, MIN {[Ass2.PointsReceived], [Ass3.Points Received]}}

Explanation

Only the highest score out of the three scores should count towards the final grade; the other two should be dropped. This can be done by subtracting the two lowest scores from the sum of the three scores.

  1. Behind Function, first select the option SUM and then click Start.
  2. Select the variables you want to add up - these are the scores of the three grade items: [Ass1.PointsReceived], [Ass2.Points Received], [Ass3.Points Received]. Separate the variables with a comma (click Next Term).
  3. Add END when you are finished with the SUM.
  4. To add what component you want to subtract, first add a minus sign (-) and then select the option MIN.
  5. Add the variables of which the smallest variable should be subtracted from the score: in this care you will first subtract the smallest score from the two variables - the variables [Ass1.PointsReceived], [Ass2.Points Received]. Again, use Next Term to separate the variables.
  6. Add END when you are finished with the MIN.
  7. Enter the second value you want to subtract from the total score. Begin with adding the minus sign (-) again. 
  8. The formula now has to calculate which of the two leftover scores is the lowest in order to subtract it. Add IF to do so.
  9. Select the MIN option. Then enter the two variables of which the lowest must be subtracted: [Ass2.Points Received], [Ass3.Points Received]  when this is also the lowest variable in the first combination [Ass1.PointsReceived], [Ass2.Points Received].
  10. Enter what will happen if IF is true, meaning what will happen if the lowest score from [Ass2.Points Received], [Ass3.Points Received] is equal to the lowest score from [Ass1.PointsReceived], [Ass2.Points Received]. In this case the lowest score from [Ass1.PointsReceived], [Ass3.Points Received] will be subtracted from the total.
  11. Add what happens when the IF is not true. In that case, the lowest score from [Ass2.Points Received], [Ass3.Points Received] will be subtracted from the total.
  12. Finish the IF with END.

Example 3

You want to award a student with a score of a 100 percent for a category, when they have scored at least 75 percent for all grade items in the category. When a student did not receive a 75 percent for all grade items, they will automatically fail the entire category.

The formula then looks as follows:

= IF {MIN{ [Ass1.Percent], [Ass2.Percent], [Ass3.Percent]} < 75, 0, 100}

Explanation

You want the students to have a score of a 100 percent for a category when students have achieved a score above the 75 percent. This can be done with the IF function:

  1. Select the option IF for the Function and then click Start.
  2. Enter the variable the students have to meet; in this case the students have to get a minimum percentage for three grade items. This means you will get a MIN behind the IF.
  3. Enter from which variable the one with the lowest percentage should be retrieved. Behind the MIN you will see  [Ass1.Percent], [Ass2.Percent], [Ass3.Percent].  Separate the variables with a comma (click Next Term).
  4. Add END to close the MIN.
  5. Enter the conditions the variable has to meet; in this case <75, which means the formula will check whether the value for the lowest scoring variable is below the 75%.
  6. Enter the next variable; namely what would happen when IF is true; in this case, the student would receive a 0, because the student has to achieve at least 75% for all grade items to pass the category. 
  7. Enter what happens when IF is not true; in this case a 100, because the student will pass the entire category when they have received at least a 75% for all variables.
  8. Finish the IF with END.
Previous Article How do I adjust settings in the grade book?
Next Article How do I exempt a student from an assignment, quiz or other activity (grade item)?