Travel rate is a term that refers to the amount of money that a person or a company pays or receives for travel expenses, such as airfare, lodging, parking, and meals. Travel rate can vary depending on the destination, the duration, the frequency, and the purpose of the travel.
One of the common tasks that involves travel rate is to calculate the total cost or reimbursement of travel expenses for a given period of time, such as a month or a year. This can be done using Excel formulas, which are expressions that perform calculations on values in a worksheet.
Procedures
To calculate the total travel expenses using Excel formulas, we need to follow these steps:
- Organize the data in a table, with each row representing a trip and each column representing a variable, such as the number of trips, the number of days per trip, the airfare per trip, the parking per day, the lodging per day, and the meal allowance per day.
- Create a formula that calculates the travel expenses for each trip, based on the variables in the table. The formula should multiply the number of trips by the number of days, and then multiply the result by the sum of the airfare, the parking, the lodging, and the meal allowance. The formula should be entered in the first row of the table, and then copied down to the rest of the rows.
- Create a formula that calculates the total travel expenses for all trips, by adding up the values in the column that contains the travel expenses for each trip. The formula should be entered in a cell below the table.
Explanation
To illustrate the procedures, let us use an example scenario with real numbers. Suppose we have the following data in an Excel worksheet:
| # of trips | # of days/trip | Airfare/Trip | Parking/day | Lodging/day | Meal Allowance/day |
|---|---|---|---|---|---|
| 4 | 3 | 550 | 11 | 160 | 30 |
| 8 | 2 | 0 | 0 | 130 | 30 |
| 1 | 4 | 550 | 11 | 110 | 30 |
| 12 | 4 | 250 | 14 | 180 | 30 |
| 1 | 4 | 0 | 0 | 110 | 30 |
| 4 | 3 | 400 | 12 | 160 | 30 |
| 8 | 2 | 0 | 0 | 130 | 30 |
| 1 | 4 | 450 | 13 | 110 | 30 |
| 2 | 2 | 0 | 0 | 200 | 30 |
| 1 | 4 | 0 | 0 | 110 | 30 |
The data represents the travel expenses for 10 trips, with different variables for each trip. We want to calculate the total travel expenses for all trips, using Excel formulas.
First, we need to create a formula that calculates the travel expenses for each trip, based on the variables in the table. The formula should multiply the number of trips by the number of days, and then multiply the result by the sum of the airfare, the parking, the lodging, and the meal allowance. The formula should be entered in the first row of the table, and then copied down to the rest of the rows.
The formula can be written as follows:
=A2*B2*(C2+D2+E2+F2)
Where A2 is the cell that contains the number of trips, B2 is the cell that contains the number of days per trip, C2 is the cell that contains the airfare per trip, D2 is the cell that contains the parking per day, E2 is the cell that contains the lodging per day, and F2 is the cell that contains the meal allowance per day.
The formula can be entered in the first row of the table, in a new column that we can label as “Travel Expenses”. Then, we can copy the formula down to the rest of the rows, by dragging the fill handle (the small square at the bottom right corner of the cell) or by using the keyboard shortcut Ctrl+D.
The result should look like this:
| # of trips | # of days/trip | Airfare/Trip | Parking/day | Lodging/day | Meal Allowance/day | Travel Expenses |
|---|---|---|---|---|---|---|
| 4 | 3 | 550 | 11 | 160 | 30 | 14820 |
| 8 | 2 | 0 | 0 | 130 | 30 | 2560 |
| 1 | 4 | 550 | 11 | 110 | 30 | 2804 |
| 12 | 4 | 250 | 14 | 180 | 30 | 22560 |
| 1 | 4 | 0 | 0 | 110 | 30 | 560 |
| 4 | 3 | 400 | 12 | 160 | 30 | 11520 |
| 8 | 2 | 0 | 0 | 130 | 30 | 2560 |
| 1 | 4 | 450 | 13 | 110 | 30 | 2604 |
| 2 | 2 | 0 | 0 | 200 | 30 | 920 |
| 1 | 4 | 0 | 0 | 110 | 30 | 560 |
Next, we need to create a formula that calculates the total travel expenses for all trips, by adding up the values in the column that contains the travel expenses for each trip. The formula should be entered in a cell below the table.
The formula can be written as follows:
=SUM(G2:G11)
Where G2:G11 is the range of cells that contains the travel expenses for each trip.
The formula can be entered in a cell below the table, in the same column as “Travel Expenses”. We can also label the cell as “Total Travel Expenses”.
The result should look like this:
| # of trips | # of days/trip | Airfare/Trip | Parking/day | Lodging/day | Meal Allowance/day | Travel Expenses |
|---|---|---|---|---|---|---|
| 4 | 3 | 550 | 11 | 160 | 30 | 14820 |
| 8 | 2 | 0 | 0 | 130 | 30 | 2560 |
| 1 | 4 | 550 | 11 | 110 | 30 | 2804 |
| 12 | 4 | 250 | 14 | 180 | 30 | 22560 |
| 1 | 4 | 0 | 0 | 110 | 30 | 560 |
| 4 | 3 | 400 | 12 | 160 | 30 | 11520 |
| 8 | 2 | 0 | 0 | 130 | 30 | 2560 |
| 1 | 4 | 450 | 13 | 110 | 30 | 2604 |
| 2 | 2 | 0 | 0 | 200 | 30 | 920 |
| 1 | 4 | 0 | 0 | 110 | 30 | 560 |
| Total Travel Expenses | 58408 |
The final result is that the total travel expenses for all trips is $58,408.
Other Approaches
There are other ways to calculate the total travel expenses using Excel formulas, such as using the SUMPRODUCT function, which multiplies corresponding values in two or more arrays and returns the sum of the products. For example, we can use the following formula to calculate the travel expenses for each trip, without creating a new column:
=SUMPRODUCT(A2:F2,{B2,C2+D2+E2+F2})
Where A2:F2 is the range of cells that contains the variables for the trip, and {B2,C2+D2+E2+F2} is an array that contains the number of days and the sum of the airfare, the parking, the lodging, and the meal allowance. The formula should be entered in the first row of the table, and then copied down to the rest of the rows.
The result should be the same as before, except that the travel expenses for each trip are not shown in a separate column, but only in the formula bar when a cell is selected.
Then, we can use the same formula as before to calculate the total travel expenses for all trips, by adding up the values in the range that contains the travel expenses for each trip. The formula should be entered in a cell below the table.
The result should be the same as before, except that the total travel expenses are not shown in the same column as the travel expenses for each trip, but only in the formula bar when the cell is selected.
Alternatively, we can use the SUM function to calculate the total travel expenses for all trips, by nesting the SUMPRODUCT function inside it. For example, we can use the following formula to calculate the total travel expenses for all trips, without creating any new columns:
=SUM(SUMPRODUCT(A2:F2,{B2,C2+D2+E2+F2}),SUMPRODUCT(A3:F3,{B3,C3+D3+E3+F3}),SUMPRODUCT(A4:F4,{B4,C4+D4+E4+F4}),SUMPRODUCT(A5:F5,{B5,C5+D5+E5+F5}),SUMPRODUCT(A6:F6,{B6,C6+D6+E6+F6}),SUMPRODUCT(A7:F7,{B7,C7+D7+E7+F7}),SUMPRODUCT(A8:F8,{B8,C8+D8+E8+F8}),SUMPRODUCT(A9:F9,{B9,C9+D9+E9+F9}),SUMPRODUCT(A10:F10,{B10,C10+D10+E10+F10}),SUMPRODUCT(A11:F11,{B11,C11+D11+E11+F11}))
Where A2:F11 is the range of cells that contains the variables for all trips, and {B2,C2+D2+E2+F2}, {B3,C3+D3+E3+F3}, …, {B11,C11+D11+E11+F11} are arrays that contain the number of days and the sum of the airfare, the parking, the lodging, and the meal allowance for each trip.
The formula should be entered in a cell below the table.
The result should be the same as before, except that the total travel expenses are not shown in any column, but only in the formula bar when the cell is selected.
