CODEWITHSUNDEEP
exceltimemedian
powiedz
powiedz

Reputation: 11

How to calculate a... median? of hour/day/month of occurence?

My problem concerns day of month however, I can see that the same logic would apply to month number or hour number or any other variable that ends on some value and then starts from 0 again.

It is defined as follows: I'm trying to calculate a day of month when a payment is made to use it for a forecast. So I have for example for one case:

1 May 2016
2 June 2016
30 June 2016
29 July 2016
6 September 2016

A simple average would give me 14th, and the median would give me 6th. But the result I'm looking for is more like the 1st.

I see I could do it somehow by calculating geometric median, or euclidean distances after placing the points on a circle etc, but I believe it can be approached in a much simpler way. I also see that solving this problem with standard means and averages would cause a situation where it gives more than one result.

But if we add an assumption that it should occur once in 30 days/a month? Wouldn't this assumption make the problem easier?

Please let me know if you solved a similar problem before or if you have any ideas

Upvotes: 0

Views: 403

Answers (1)

DMM
DMM

Reputation: 1112

If the result you are "looking for is more like the 1st", then I would hazard a guess that you are really looking at a series of monthly payments (perhaps falling due on the first of each month or the first working day of each month) and you want some measure of the deviation between the due date and the actual date of payment.

If that is the case then simply calculate the difference in days between the due date and the actual date of payment for each monthly payment (following a consistent convention such as positive values denote late payment and negative values are early) and then apply your chosen measure (median, mean, etc) to the series of differences.

Upvotes: 1

Related Questions