There are lot of free spreadsheet templates available for Calculating Travel Expenses. Here we are going to see the steps required for writing a simple Travel Expenses Tracker in Google Spreadsheet.
The Travel Expenses Tracker Spreadsheet has two blocks
- First block includes the names of each person, total money spent by them and balance to be paid.
- Second block has the expenses incurred during the trip.
The following Google Spreadsheet function and features are used in this Spreadsheet.
A drop down option is available in the second block to pick the name of the person from the first block using Data Validation.
Under Cell range, specify the column for Data Validation and values for drop down is picked from the range which contains the Person name.
The Total Expenses is calculated by adding the amount specified in the H column SUM(H4:H21) i.e SUM(Amount)
Share for each person(H26) is calculated using the formula =ROUND(H24/COUNT(A4:A21)) i.e ROUND(Total Expenses/COUNT(No.of.Person)). If you are going to add extra person then make sure to add the entry in No column. ROUND function has been used to avoid decimal numbers.
Money spent by each person has been calculated using the formula IF(B4=””,””,SUMIF(F$4:F$21,B4,H$4:H$21)). This uses SUMIF function for adding the total amount spent by each person by comparing the name specified under column B. The IF function is used for checking empty values.
The Balance amount that needs to be paid by each person is calculated using the Formula =IF(B4 = “”,””,H$26–C4) i.e Share/Person minus Money Spent by Individual person. Negative amount indicates the person needs to receive money.
You can check the this version of Expenses Tracker Spreadsheet here.
Please feel free to share suggestion and comments.