Calculated Fields
Calculated fields allow you to perform mathematical calculations within a form. Formulas can be added to a number or currency field that will calculate entered values based on the formula.
BODMAS
Calculations are constructed in a similar style to a basic Excel formula. Within Edit Calculations, you are able to add, subtract, multiply and divide numbers or currency (including numbers that have been used in single/multiple choice questions). You can also use a Sum or Average function where multiple number responses are present.
The order of calculation will follow the BODMAS principle, explained below:
Brackets | ( ) |
Orders/Others | Orders are square roots or indices (sometimes called powers or exponents, square numbers and cube numbers). For example, 2³: the little 3 means that you multiply the number 3 times, 2 x 2 x 2 = 8. A square root is the inverse of a square number, so √25, the square root of 25, is 5 because 5 x 5 or 5² equals 25. |
Division: ÷ | Splitting into equal groups or parts |
Multiplication: x | Groups of |
Addition: + | The total of numbers together |
Subtraction: - | To take away numbers from other numbers |
Anything in brackets should be completed first, then the orders, followed by any division or multiplication, and finally addition or subtraction.
Division and multiplication have been grouped together as they are of the same level; this means that if you have a calculation involving division and multiplication then you complete them as they appear from left to right. This is the same for addition and subtraction; they are completed as they appear from left to right.
Important: It’s crucial to plan any calculations before creating them. Understanding what you want to calculate, your process, and how you want those results displayed will impact your calculated fields experience.
Creating a calculation
Within a form you may wish to calculate multiple fields together and store that information within the same form.
e.g.: Total Amount Requested can be be calculated by adding together the field Amount Requested Year 1 + Amount Requested Year 2 + Amount Requested Year 3.
Calculations are refreshed when any update to a field with a formula is entered.
To begin, highlight the field that you want to display the result of the calculation and select Edit Calculation.
You are then required to select the fields you wish to calculate using the Insert Field drop-down menu.
Build your formula now by adding the formula using the mathematical symbols available.
Or see a demonstration of creating a calculated field in the example below:
Once a calculation has been entered, the field that contains the calculation is greyed out so that the formula is unable to be overridden.
You can set the output number’s decimal places using the Format drop-down menu in the control panel.
When including multiple choice fields, you will need to decide if you want to SUM or AVG (Average) the responses before using them in a further calculation.
Once a formula has been created within a field, the field will include a hint showing that it contains a calculation. This hint can be overwritten if the user wishes. The field will also be greyed out, not allowing a value to be directly entered into to prevent the formula from being overwritten.
For number fields, the default hint is in "Must be a number. This number/amount is calculated."
For currency fields, the default hint is "Must be a dollar amount. This number/amount is calculated."
Calculations in an Assessment form
Let's look at an example of calculating fields within an Assessment form.
If I have asked my Assessors to submit a score out of 5 using a single choice question, I can automatically calculate a total score within this same form.
Likewise, if I add a weighting to my scores, I can calculate the weighted score for each assessment and compare.
In the example below, I will use a % weighting for each of the rating scores. When I create the calculation, I will make sure that each weighted percentage adds up to 100%.
With all my weighted ratings adding to a total of 100% (20% for question 1, 20% question 2 etc...) it is then a matter of calculating the highest possible score to then equal the result of the weighting.
In other words, if my assessor rates every question with a score of 5, my weighted percentage score will show as the highest possible score for 100%.
For instance:
The highest possible score in the first question in our calculation is 5 (5= excellent, 4= good, 3= satisfactory etc...)
The weighted score for this question (label C1 Objectives Met) is 20%.
5 x ?=20 or 𝟓 𝒎𝒖𝒍𝒊𝒑𝒍𝒊𝒆𝒅 𝒃𝒚 𝒙 = y (where y = weighting %)
Using BODMAS rules, and applying the above scenario across all our scored questions, this is what the final calculation looks like:
Calculations across forms
Cross form calculations can be created within the Edit Calculation window. In this window, select More and then select a field from another form.
When adding a field from another form, you can choose to include either a SUM or AVG (Average) which will sum or average the multiple responses to that form's question. This is to ensure all possible responses to that question are captured within the calculation.
E.g.:
Below is a breakdown of the functions within the Edit Calculation field.
If you have a calculation which references another form field within its formula, and if the value of the form field that is referenced within the formula is updated, then the form that contains the calculation result will not be updated automatically. The result only propagates through if the form is saved.
Calculations and Standard Fields
You can create a calculation within a number or currency field whether the field is a Standard Field or not.
A Standard Field may hold the result of the calculation, and will also have all the properties of a Standard Field.
A calculation on a standard field occurs at form level - for example, a Standard Field used in an Assessment Form may have a calculation against it to calculate the response, but that same Standard Field when inserted into an Administration form won’t carry that same calculation in its question settings.
This field will continue to behave as any other Standard Field would.
Warning: Standard Fields that have a calculation built into it on a form can still have a submitted form response overridden/edited; either on the Standard Fields tab, or within another form it has been fed through to (if not marked read-only). If you make this change, you will override the calculated response.
If you have a calculation which references a Standard Field within its formula, and if the value of the Standard Field that is referenced within the formula is updated, then the form that contains the calculation result will not be updated automatically. The result only propagates through if the form is saved.
Warnings in Calculations
If a field that is part of a calculation has been deleted, an orange warning message will appear at the top of the screen once you save the change.
When calculated fields depend on other calculated fields, the calculated fields being referenced will be calculated first, so they return expected results. Circular references will display an error when setting up the calculation in the Form Editor.
If calculation formulas are edited while a round is 'live', all previously submitted applications will use the prior calculation. The formula will only be recalculated if the response is reopened and saved, after the formula has been updated.
Circular references
If a calculation has a circular reference, which can occur by referring to a field which in terms refers back to the same field where you are editing a calculation, an error will be displayed in the form editor, and SmartyGrants will be unable to perform the calculation when a user fills out the form.
