How to use formulas to enhance your Sheetgo forms?
With the Sheetgo Forms, you can combine a variety of configurations to ensure that your forms are always submitted with the right type of data. You can also combine the header configurations with familiar spreadsheet and mathematical functions to effortlessly perform calculations inside the form, improving efficiency and accuracy in data entry.
You can use this feature to combine key spreadsheet calculations like:
SUM (adding values)
AVERAGE (calculating the mean)
COUNT (counting numeric fields, excluding text)
COUNTA (counting non-empty fields)
MAX (finding the highest value)
MIN (identifying the lowest value)
You can also use simple mathematical symbols such as: + (addition), - (subtraction), * (multiplication), and / (division).
To create a dynamically calculated field, the configuration formula must be used followed by the formula expression. To indicate another field, use this field name enclosed by single or double quotes.
Below you will find some examples of expressions that can be used:
[formula: "headerTitle1" + "headerTitle2"]
[formula: "headerTitle1" - "headerTitle2"]
[formula: "headerTitle1" * "headerTitle2"]
[formula: "headerTitle1" / "headerTitle2"]
[formula: ("headerTitle1" / "headerTitle2") * 100]
[formula: ("headerTitle1" * "headerTitle2") / "headerTitle3"]
[formula: SUM("headerTitle1", "headerTitle2")]
[formula: COUNT("headerTitle1", "headerTitle2", "headerTitle3")]
[formula: SUM("headerTitle1", "headerTitle2", "headerTitle3") / "headerTitle1"]
This feature can be extremely useful in a Purchase Order form or a similar type of form where you have the price and quantity of an item to be filled.
On the spreadsheet, the header configuration will look similar to the one above. In this case, the Total Cost field is calculated by the value added on the "Price per order" field multiplied but the number of "Amount of orders".
The final result on the form will look like the image above. You can see that since we are using the locked configuration the user is not able to make changes on the Total field.
Note that if [locked] is not used, the field will be open for user input, resuming its calculation if no value is provided.
Errors in the field or calculation are presented in the same way as the ones in a spreadsheet, so invalid values will produce #VALUE!, unsupported or wrong formula names will result in #NAME!, and self-references #REF!, allowing the users who are familiar with spreadsheets to determine the error nature easily.
Need more help?
Contact us via chat, on our email ([email protected]), or send us a ticket!
Visit our Community for help, tips, tricks, and feedback for Sheetgo.