Average of Top Eight (and five) Records in Multiple Columns and multiple Days (in rows)

[GeoffH1]

Hi. I have 54 columns of data. Each column has been grouped by Month, Day and Hour (via a Pivot Table) to show the average number on each hour of each day, over 8 months (1st October 2011 to 31st May 2012).
I require to get the average of the highest eight numbers on each day (there are 244 days) for each column. I also require to get the average of the highest five numbers on each day, for each column.
One problem is that the lowest number is repeated sometimes, but I only require to get the average of eight (and five) numbers, for each day. I used Conditional Format to highlight the top eight hours (in red font and background) and the top five hours (in bold font) which illustrates the problem. The C.F. is correct in identifying the top numbers, but I only need eight (and five) numbers for each calculation.
Another issue is that data is missing sometimes: these days are to be ignored; I only require to get the averages from days that contain all 24 records.
I may not have the data set up in the best way, having left it in Pivot table format, except for the first day, where I have inserted 4 rows and calculated the two averages above, for the first four columns.
Any help would be greatly appreciated, again. Regards, Geoff.

Hopefully attached Excel file, with only 18 columns now (reduced from 54) to conform to this Forum's size limit.

[CAntosh]

Try toying around with this format - these are array formulas, so copy them in and confirm them with Ctrl + Shift + Enter rather than the usual Enter:

For top 8:
=AVERAGE(LARGE(C$8:C$30,{1,2,3,4,5,6,7,8}))

For top 5:
=AVERAGE(LARGE(C$8:C$30,{1,2,3,4,5}))

If you only want a return when all hours are filled:
=IF(COUNTBLANK(C$8:C$30)>0,"",AVERAGE(LARGE(C$8:C$30,{1,2,3,4,5,6,7,8})))
=IF(COUNTBLANK(C$8:C$30)>0,"",AVERAGE(LARGE(C$8:C$30,{1,2,3,4,5})))

[GeoffH1]

Thanks cantosh, your second two formulae work well for what I need but I am not sure what you mean by using the array (CS&E) method. Do I need to re-structure the data to use the array formula?

[CAntosh]

You shouldn't need to restructure anything. The CS&E method is required for most array-calculation formulas in order for them to return the proper result. I may have been a bit hasty in declaring that the formulas in post #2 need CS&E. They appear to work fine with the standard enter. If it works, it should be fine as is.

[GeoffH1]

I agree, it works well as it is. After deleting the 8 rows with Oct, Nov etc, and removing the ($'s) from the formula, then I have just copied the formula down every 25 rows, and copied across to get all the results for each column. Will mark as Solved. Thank you very much for the quick response. Regards, Geoff.

[CAntosh]

Glad to help, good luck!

[GeoffH1]

Addendum to Post#1. In the attached Worksheet, I was missing the very first row of data, i.e. the 00 hour for October 1st, which resulted in only 23 rows occurring this day. Therefore, for future reference, cantosh's formulae should read:

=IF(COUNTBLANK(C$8:C$31)>0,"",AVERAGE(LARGE(C$8:C$31,{1,2,3,4,5,6,7,8})))
=IF(COUNTBLANK(C$8:C$31)>0,"",AVERAGE(LARGE(C$8:C$31,{1,2,3,4,5})))

This captures all 24hours of each day and also when copied down through the days.

My fault cantosh. Previously, I had to correct for the clock change to convert to Irish Summer Time, which resulted in an additional hour being added on June 1st (which I deleted) but I failed to notice that the first hour of October 1st was missing! My apologies for any confusion.