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.
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.
Creating a calculation
Within a form you may wish to calculate multiple fields together and store that information within the same form.
eg: 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" dropdown menu
Build your formula now by adding the formula, using the mathematical symbols available
Or see demonstration of creating this example:
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 number of the decimal places using the format dropdown menu in the control panel.
When including multiple choice fields you will need to decide if you want to SUM or Average the responses before using 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 and overwrite the formula.
- For number fields the hint is in "Must be a number. This number/amount is calculated."
- For currency fields, the hint is "Must be a dollar amount. This number/amount is calculated."
Calculations in an Assessment form
Lets 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 calculate the result within the form and automatically calculate a total score.
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 have added a % weighting to each of the rating scores. I have made sure that each of these percentages add 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 as excellent my percentage will show as 100%. For instance:
- the highest possible score in the first calculation below example is 5 (5= excellent, 4= good, 3= satisfactory etc...)
- the weight score is 20%.
- 5 x ?=20 or 𝟓 𝒎𝒖𝒍𝒊𝒑𝒍𝒊𝒆𝒅 𝒃𝒚 𝒙 = y (where y = weighting %)
Using BODMAS this is what the calculation looks like:
Calculations across forms
Cross form calculations can be created within the "Edit Calculation" window by selecting More and then selecting a field from another form.
When adding a field from another form you can choose to include either a Sum or 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.
eg:
Below is a breakdown of the functions within the Edit Calculation field.
If you have a calculation which references another form field within it's 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 may have a calculation in an Assessment form but that same standard field when used in an Administration form might not have the calculation in it.
This field will continue to behave as a any other Standard Field would, in summary:
- Standard Fields lock down the label
- Responses can flow through from one form to another (with a read only option)
- Administrators can customise standard fields for your organisation
- Users can edit the value/response on the Summary tab or any form field response that feeds into the application
If you have a calculation which references a standard field within it's 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 field, which in terms refers back to the 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.
