How can I average the last 30 entries by date?

[geordie]

Hi everyone. This is my first time posting and I'm a bit of a newbie to excel but am learning so much from your excellent site.


I have a list as below:

A B C D E F G H

Ref Type Project Manager Customer Name Assigned date Go Live Status TTD

790 Move VF-UKEvansS8 Juice Telecoms Ltd - Office Move 13/10/2011 25/10/2011 Complete 9

779 New vf-ukborthwicka Accelerate Cleaning Solutions Ltd 03/10/2011 01/11/2011 Complete 21


I am trying to average the TTD in column H only for those with a type New in column B and only the most recent 30 using the go live dates in column F.


I have tried various things from the posts I have read but am not getting anywhere. I would be really grateful for any help you can give me on this.


Regards Geordie

 

[OleMiss2010]

The simplest way that I can think of would be to sort by Column B then Column E and, in some cell, apply the average formula for the thirty most recent "New" projects..


This accomplishes your goal, but if this is something you will need to do on a constant basis then we will have to go about it a different way.

 

[OleMiss2010]

Also welcome to Chandoo forums.

 

[NARAYANK991]

Hi Geordie ,


Can you check out the following formula in the post whose link is given below ?


=OFFSET(Sheet2!$J$8,0,COUNTA(Sheet2!$J$8:$AG$8)-13,1,13)


http://chandoo.org/forums/topic/using-counta-and-offset-formulas-for-13-month-dynamic-graphs


The number 13 used in the formula is used to get the 13 most recent entries ; in your case it would be 30.


You need to clarify whether the 30 most recent entries are only the 30 which are in the bottom-most 30 cells of your data column , or are they 30 entries in date-wise order ?


Narayan

 

[geordie]

Hi, thanks for both of your replies. I would like to do it with a formulae but what I can't get my head around is how to get the last 30 entries by date as they don't come in date order (unfortunately). Any ideas?

 

[Hui]

Geordie


This should do it:


=SUMPRODUCT((B3:B100="New")*(F3:F100>LARGE(F3:F100,31))*(H3:H100))/SUMPRODUCT((B3:B100="New")*(F3:F100>=LARGE(F3:F100,30)))


Change the Range 100 to suit your data

Note that the top 30 is found by > LARGE(F3:F100,31)

Not >= Large 30 in case there are multiple entries equal to the value of the 30th entry

 

[geordie]

Hi Hui, thanks for the post. Really appreciate it. But...


Your formulae brings back a result but what it is doing is taking the top 30 entries by date (good so far) then filtering out the non "New"s and averaging what is left. Any ideas?