Finding Median Average Number of days at a place

[5pac3m0nk3y]

HI,



I am trying to create a report that shows the median average number of days a group of people spent in a place for two fiscal years -- 2016 and 2017.
I got everything set up. I count the difference between the date they came in and the day they left. Then I just plop it into excel and use the median formula. But I'm not sure what to do about people who came in before 2016 spending sometime in in 2015 and some in 2016. Should I subtract the days they were in in 2015 if I'm trying to get the median for 2016 only? Or should they not count at all since they came in in 2015? I guess this a bit of a math question as well.

Thanks,
Al

[Micron]

Someone once said something along the lines of "Tell me what you want the stats to reveal and I'll make them do just that". I think the answer is based on what you want to know and the definitions of the aspects involved. A person who has their 2015 days subtracted from the grand total can be compared to someone who was there for the entire year. If the second person started January 01, then the number is the same. If they didn't then the approach is still valid for the question. If the question is who was in one place the longest, then to subtract 2015 days would be incorrect.

[5pac3m0nk3y]

Someone once said something along the lines of "Tell me what you want the stats to reveal and I'll make them do just that". I think the answer is based on what you want to know and the definitions of the aspects involved. A person who has their 2015 days subtracted from the grand total can be compared to someone who was there for the entire year. If the second person started January 01, then the number is the same. If they didn't then the approach is still valid for the question. If the question is who was in one place the longest, then to subtract 2015 days would be incorrect.

Hi Micron,

Thanks for your reply. I think the person I'm working for wants to know the median average number of days a person stayed in a location for 2016, 2017, and for both years combined.

The problem is that not everyone came in and left in 2016 or in 2017. Some came in 2015 and stayed all the way til 2017. Or others came in 2015 and left sometime 2016.

So if I want the median number of days a person stayed here in 2016 I'm sorting people by the day they came in -- >=#1/1/2015# And <=#1/1/2016#
Then I'm counting the days of any people shown using DateDiff between the in and out date.
So if I count the days they were here in 2015 it will change the median average compared to counting only the days there were here in 2016 when I have a long list of people with varying numbers of days. No?

Regards,
Al

[5pac3m0nk3y]

Let me give you a concrete example. If I gathered together all the people who arrived in 2015 and left in 2016, counted the number of days including 2015, calculated the median, I will get a different number than if I only look at people who came in and left in 2016.

[Bulzie]

If your customer only wants 2016 and 2017, then I think you need to exclude any time before or after those years. So if they worked 2 months in 2015 and 2 months in 2016, they only count for the 2 in 2016.

But if you want to be sure, clarify it with your customer to make sure, not us.

[5pac3m0nk3y]

Bulzie,

Thanks. Its all taken care of. The customer has no idea so I just used in and out dates that fell only within those years just to make it easier. Have a nice day.