Advanced excel doubts - periods with different "k"(s)

[franciscocorrea]

Hello everyone!

Imagine I want to get the top-3 "Value" of each period of three days (in this case from 30/4 until 2/5, the top-3 values would be 78, 70, 68), aditionally I want to roll this top-3 over the time, so the second in analysis period would be (1/5 until 3/5), the third (2/5 until 4/5), and so on, until today. How do I do this in excel, is it possible? Below I draw a simple table just to make it easier to understand! I want to color those values which belong to the top-3 with another colour (lets say green), obviously, there are some "Value" that will belong to the top-3 of different periods, in my example the value 78 of idea "I": 1st, 2nd and 3rd period.

Idea	Day	Value
A	30/4/12	56
B	30/4/12	60
C	30/4/12	70
D	30/4/12	34
E	1/5/12	67
F	1/5/12	68
G	1/5/12	45
H	2/5/12	23
I	2/5/12	78
J	3/5/12	66
(...)	(...)	(...)
n th idea	31/10/12	67

<tbody>
</tbody>

Thank you very much, I am waiting for your feedback

Best regards,
Francisco Corrêa

 

[franciscocorrea]

Difficulty in Rolling periods

Hello everyone!

Imagine I want to get the top-3 "Value" of each period of five days (in this case from 30/4 until 4/5, the top-3 values would be (78, 70, 68), aditionally I want to roll this top-3 over the time, so the second period in analysis would be (1/5 until 5/5), the third (2/5 until 6/5), and so on, until today.

Attention: I have no observations for all days, so from observation of 30/4 until observation of 3/5 there is 2 days in between that must be taken in account to form the 5-day period. The main difficult here is that as I say, there are no observations for all days, neither the number of ideas in one day is constant.

I want to color those values which belong to the top-3 with another colour (lets say green), obviously, there are some "Value" that will belong to the top-3 of different periods, in my example the value 78 of idea "I": 1st, 2nd and 3rd period.

How do I do this in excel, is it possible? Below I draw a simple table just to make it easier to understand!

Idea	Day	Value
A	30/4/12	56
B	30/4/12	60
C	30/4/12	70
D	30/4/12	34
E	3/5/12	67
F	3/5/12	68
G	3/5/12	45
H	4/5/12	23
I	4/5/12	78
J	5/5/12	66
K	5/5/12	70
L	8/5/12	56
M	9/5/12	88
N	9/5/12	76
O	9/5/12	88
P	9/5/12	65
Q	10/5/12	76
R	10/5/12	77
(...)	(...)	(...)
n th day	31/10/12	68

<tbody>
</tbody>

Thank you very much, I am waiting for your feedback

Best regards,
Francisco Corrêa