Formula fields can be added within a spreadsheet to add, subtract, divide, or multiply content added to the following fields: Drop down, checkboxes, radio buttons, number, and mileage. Formula fields can be added to sections to calculate the sum, average, field count, minimum value, or maximum value of these fields contained within the spreadsheet.
Here are steps to create a Mileage Expense Form with Formula fields.
Start by creating a Blank Form. Add in a section for Employee information, and insert a User Data field and a Date field for the Date Submitted. Then add in a Spreadsheet for the employees expenses, with a date field for the date of travel.
Now insert your mileage field - you can choose to show the "to" and "from" locations on the form by ensuring that the "show location summary" box is checked. Un-checking this box will only show mileage.
In the "spreadsheets" formbuilder video on intranetconnections.com we created spreadsheet formulas using a drop down box so here we will choose radio buttons. We want to add in hidden values so that content is associated with a numeric value. Hidden values can be added to radio button fields, drop down box fields, and checkboxes fields.
Insert a radio button field and click the "hidden values" box. We want to reimburse employees differently depending which type of car they were driving because certain cars use more gas than others. Beside options click the green plus icon and enter in a car name and a value for reimbursement (ex. 0.50 for 50 cents per mile). Do that a couple more times with different car types and different values.
Once you have listed all of your types of cars and assigned values to each you need to close out and save your changes. Now when we insert a formula field into our spreadsheet the drop down box beside Formula will give us the option to select either Mileage or Car type. We select Mileage, the multiplication button(*), and then Car Type so that we times our mileage by the hidden values we have given for each car type. When we close out our form now looks like this:
Next we want to add in a "Claim Total" field, which will add up all of the amounts generated per trip, to display a total expense value at the top of the form. To do this we have to first SAVE OUR CHANGES. Then we click on our "Employee Information" section and add in a formula field. Beside the formula field we choose Spreadsheet.Amount, which will pull information from the "Amount" field in our spreadsheet - and we choose SUM to add up all the rows.