How to add a calculated field in Google Form?

"Why can't Google Forms allow users to add simple calculated fields in Google Forms?"- Google Docs Editors Community

Anyone who takes orders or registrations in Google Forms runs into this. The total depends on the quantity, the price and the shipping, and Google Forms can't add it up. People submit without knowing what they owe, and you work out each total and write back. A formula in the linked Google Sheet calculates it, but only after they submit. With Formfacade, you write a formula in a field's Calculate option and people watch the total change as they fill in the form. You can add conditions too, such as free shipping above a set amount.

Below are three ways to get a calculated value from a Google Form, and where each one falls short.

Calculated fields allow you to compute a field with values from other fields. Calculated fields can perform arithmetic on number fields, manipulate text & date. For example, you can calculate the total amount in order form based on the order quantity and shipping option chosen by the user.

Important

We have simplified the calculations for standard use cases such as calculating total amount and displaying order summary in order forms, calculating scores and providing verbal assessments for surveys and questionnaires.
Order forms

3 ways to get a calculated value

1. Add a formula in the linked Google Sheet

Link the form to a Google Sheet and add a column with a formula, such as the quantity multiplied by the price.
Where it falls short: the formula runs only after the form is submitted. People don't see the total while they fill in the form, so you reply to each one with the amount.

2. Use Google Apps Script

Write a script with an on form submit trigger that works out the value and emails it to the person who filled in the form.
Where it falls short: the script also runs only after submit, and it is code you have to write and maintain. Nothing in Google Forms can show a value while the form is being filled in.

3. Calculate in the form with Formfacade

Let's create a simple order form and learn how to add a calculated field to compute and display the order amount to the user filling the form. Users can order one or more bottles of perfume using the order form. The standard price is $60, but if they order 10 or more bottles of the perfume, the price per bottle is $50. There is a standard shipping fee of $25, but this fee is waived off for orders above $150.
Calculations to be added in the order form:
Product price = $60 for < 10 bottles of perfume; $50 for >=10 bottles of perfume
Order amount = Quantity * Price
Shipping fees = $0 for order amount > 150; $25 for order amount =< 150
Total amount = Order amount + Shipping fees

Add the questions in the Edit tab

  • Click on Create your form, name your form, click Build form and click Proceed.
  • Click on the "Edit" tab
The tabs Edit, Preview, Share and Responses, with Edit marked
  • Click on the + icon in the toolbar on the right and add a short answer question for each of these fields: How many perfumes would you like to order?, Order amount, Shipping fees and Total amount
  • For the How many perfumes would you like to order? question, click on the ⚙️ icon, select the Answer tab, click Configure under Validation, select Number and select Is number. Click Save.
The Order Form in the Formfacade editor with the EDIT tab marked and the fields Name, Email, How many perfumes would you like to order? and more

Simple calculation

We have to add the order amount and shipping fees to calculate the total amount. It's a simple addition.
Order amount + Shipping fees
In the Edit tab, click on the Total amount field, then click on the ⚙️ icon. Select the Answer tab and write the above formula in the Calculate option. Click Save.
To use a form field in the calculation, type @ and you will see the list of all the fields in the form. Select the required form field to add the field. You will see the display name of the field, but internally it uses the entry id for the calculations.
The field settings on the Answer tab with Order amount + Shipping fees in Calculate and the Answer tab, Calculate and Save marked

Conditional logic

The standard shipping fee is $25, but this fee is waived off for orders above $150. To implement this calculation, we have to check if the order amount is greater than 150.
You can use the IFS function to write conditional logic. IFS function evaluates multiple conditions and returns a value that corresponds to the first true condition. The syntax for the IFS function is:
IFS(condition1, value1, [condition2, value2, …])
where
  • condition1 - The first condition to be evaluated.
  • value1 - The returned value if condition1 is TRUE.
  • condition2, value2, … - Additional conditions and values if the first one is evaluated to be false.
The formula to calculate the shipping fees is:
IFS(Order amount > 150, 0, 25)
In the Edit tab, click on the Shipping fees field, then click on the ⚙️ icon. Select the Answer tab and write the above IFS formula in the Calculate option. Click Save.
Note: To use a form field in the calculation, type @ and you will see the list of all the fields in the form. Select the required form field to add the field. You will see the display name of the field, but internally it uses the entry id for the calculations.
The field settings on the Answer tab with IFS(Order amount > 150, 0, 25) in Calculate and the Answer tab, Calculate and Save marked

Conditional logic + Calculations

Order amount = Quantity * Price
Quantity is entered by the user. Based on this quantity entered, we have to calculate the price. The standard price is $60, but if a user orders 10 or more bottles of the perfume, the price per bottle is $50. We can use the IFS function to determine price and then multiply it with quantity to calculate the order amount.
IFS(Quantity >=10 , 50, 60) * Quantity
In the Edit tab, click on the Order amount field, then click on the ⚙️ icon. Select the Answer tab and write the above formula in the Calculate option. Click Save. Here, Quantity is the How many perfumes would you like to order? question.
Note: To use a form field in the calculation, type @ and you will see the list of all the fields in the form. Select the required form field to add the field. You will see the display name of the field, but internally it uses the entry id for the calculations.
The field settings on the Answer tab with IFS(Quantity >= 10, 50, 60) * Quantity in Calculate and the Answer tab, Calculate and Save marked
The user filling the form will see the order amount, the shipping fees and the total amount as they enter the quantity.
The Order Form filled in by John Wick for 1 perfume, with Order amount $60.00, Shipping fees $25.00 and Total amount $85.00

Order form

You can test this order form using the link below. https://bit.ly/formfacade

Summary

Google Forms has no calculated field, and a formula in Google Sheets or a script runs only after the form is submitted. With Formfacade, you write a formula in a field's Calculate option and the value is computed and shown in real time to the person filling in the form. Create your form and complete the steps above. If you have any questions, write to support.

Last updated: