0

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").

Anna
  • 1
  • 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. – JMoravitz Jul 10 '23 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 Jul 10 '23 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. – JMoravitz Jul 10 '23 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 Jul 10 '23 at 15:49
  • Repeating myself: "I expect that the second method is what you intend as it is the more natural question to ask" – JMoravitz Jul 10 '23 at 15:51
  • @Anna Take a look at this. When different populations are averaged you must use weighted averages. If you do that then both methods will yield the same result. – John Douma Jul 10 '23 at 17:18

1 Answers1

1

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.

You could also ask "if I were to combine the bags, and then you pull out a bill at random from the combined bags... what is the expected amount of money you would pull out?" That would be answered like your second approach here.

Both questions are fine questions to ask... and the questions have different answers and the answers are calculated differently.

The fundamental difference here is again whether you want the trips to have equal weight despite being different lengths, or if you want each individual day to have equal weight.

JMoravitz
  • 79,518