# How to calculate average rate from a dataset

I have a large dataset of "trips", each row is a different trip. I want to know the average number of miles per day for a group of trips.

Example data:

Trip 1: 500 miles in 30 days (16.7mi/day)

Trip 2: 200 miles in 10 days (20mi/day)

Trip 3: 30 miles in 3 days (10mi/day)

Trip 4: 50 miles in 6 days (8.3mi/day)

I calculated the average 2 different ways:

1. Created a calculated column "miles per day" for each trip, shown in the example in parentheses. Then calculated AVG(miles per day) -> (16.67 + 20 + 10 + 8.3)/4 = 13.8mi/day
2. (SUM(miles))/(SUM(days)) -> (500+200+30+50miles) / (30+10+3+6days) = 15.9mi/day

With the dataset I'm using, the 2 methods always give me different results. I am wondering if anyone can explain which method would be more reflective of the "true" average miles per day for a group of trips, and why that method is preferred. (Note: for the purpose of this dataset, it is possible that some trips had a negative "miles per day value").

• They are both correct answers to different questions. To answer which is preferred, you must clearly define what you mean by "average" for this scenario. I expect that the second method is what you intend as it is the more natural question to ask and the reason why it came out differently is very simply because you want each day to be weighted the same in the average, not that each trip be weighted the same. Commented Jul 10, 2023 at 15:13
• The first method is what I was originally intending until the second method came up as another approach. This is a hypothetical example and I am trying to understand what the theoretical difference between the two methods are and why, so I can discern which method to use for my true dataset.
– Anna
Commented Jul 10, 2023 at 15:28
• Again... it depends on what you want. What are you trying to ask? Are you trying to describe the average amount of distance traveled in a single day of someone who went on all of these trips consecutively and so want to average the result of each day together? Or are you offering a single one of these trips uniformly at random to somebody and asking what the distance traveled in a single day during that trip would be? Again, these are both correct answers but to different questions. Commented Jul 10, 2023 at 15:38
• These trips all occurred at different times and with different people. I want to look at the average mi/day of different groupings of the trips based on other characteristics in the dataset. The miles/day of one trip alone is not relevant to me
– Anna
Commented Jul 10, 2023 at 15:49
• Repeating myself: "I expect that the second method is what you intend as it is the more natural question to ask" Commented Jul 10, 2023 at 15:51

Imagine another scenario... you have two bags. In the first bag, you have a single \$100 bill as well as a single \$1 bill. In the second bag you have a hundred \\$1 bills. You could ask the question... "if I were to give you a bag at random, and then you pull out a bill at random from that bag... what is the expected amount of money that you would pull out?" That would be answered like your first approach here.