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.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Idea[/TD]
[TD]Day[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]30/4/12[/TD]
[TD]56[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]30/4/12[/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]30/4/12[/TD]
[TD]70[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]30/4/12[/TD]
[TD]34[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]1/5/12[/TD]
[TD]67[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD]1/5/12[/TD]
[TD]68[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD]1/5/12[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD]H[/TD]
[TD]2/5/12[/TD]
[TD]23[/TD]
[/TR]
[TR]
[TD]I[/TD]
[TD]2/5/12[/TD]
[TD]78[/TD]
[/TR]
[TR]
[TD]J[/TD]
[TD]3/5/12[/TD]
[TD]66[/TD]
[/TR]
[TR]
[TD](...)[/TD]
[TD](...)[/TD]
[TD](...)[/TD]
[/TR]
[TR]
[TD]n th idea[/TD]
[TD]31/10/12[/TD]
[TD]67[/TD]
[/TR]
</tbody>[/TABLE]


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!

[TABLE="width: 500"]
<tbody>[TR]
[TD]Idea[/TD]
[TD]Day[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]30/4/12[/TD]
[TD]56[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]30/4/12[/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]30/4/12[/TD]
[TD]70[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]30/4/12[/TD]
[TD]34[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]3/5/12[/TD]
[TD]67[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD]3/5/12[/TD]
[TD]68[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD]3/5/12[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD]H[/TD]
[TD]4/5/12[/TD]
[TD]23[/TD]
[/TR]
[TR]
[TD]I[/TD]
[TD]4/5/12[/TD]
[TD]78[/TD]
[/TR]
[TR]
[TD]J[/TD]
[TD]5/5/12[/TD]
[TD]66[/TD]
[/TR]
[TR]
[TD]K[/TD]
[TD]5/5/12[/TD]
[TD]70[/TD]
[/TR]
[TR]
[TD]L[/TD]
[TD]8/5/12[/TD]
[TD]56[/TD]
[/TR]
[TR]
[TD]M[/TD]
[TD]9/5/12[/TD]
[TD]88[/TD]
[/TR]
[TR]
[TD]N[/TD]
[TD]9/5/12[/TD]
[TD]76[/TD]
[/TR]
[TR]
[TD]O[/TD]
[TD]9/5/12[/TD]
[TD]88[/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD]9/5/12[/TD]
[TD]65[/TD]
[/TR]
[TR]
[TD]Q[/TD]
[TD]10/5/12[/TD]
[TD]76[/TD]
[/TR]
[TR]
[TD]R[/TD]
[TD]10/5/12[/TD]
[TD]77[/TD]
[/TR]
[TR]
[TD](...)[/TD]
[TD](...)[/TD]
[TD](...)[/TD]
[/TR]
[TR]
[TD]n th day[/TD]
[TD]31/10/12[/TD]
[TD]68[/TD]
[/TR]
</tbody>[/TABLE]


Thank you very much, I am waiting for your feedback

Best regards,
Francisco Corrêa