Hi Biff
Thanks for your reply
I used columns O and P as a guide to save space
the columns and cells I use are AI34 and AK34 so I adjusted the
formulas accordingly,but it comes up with a #REF! error where
the result should be
I also done the same as your email using columns O and P and I
got the correct answer for the one set of 10 numbers the other
sets of numbers show a #NUM! error
--
bill gras


"Biff" wrote:

Ooops!

Left out some info!

Where there are blank cells in column O, the formula will return zeros in
the corresponding cells in column P. These zeros are used in the formula as
a "marker" to determine where the next range starts. You can suppress the
display of these zeros by using a custom format of:

0;-0;;@

Just be aware that the zeros are there if you need to do further calcs on
the data.

Biff

"Biff" wrote in message
...
Hi Bill!

Will there always be at least 5 numbers to average?

I have a solution but the result is placed next to the last cell that is
not blank. Like this:

O P
1 2
2 20
3 4
4 6
5 14
6 12
7 8
8 10
9 16
10 18 16 (result)
11 blank cell

Enter this formula in P1:

=(O1="")*1

Enter this formula in P2 as an array using the key combo of
CTRL,SHIFT,ENTER:

=IF(O2="",0,IF(AND(O2<"",O3=""),AVERAGE(LARGE(IND IRECT("O"&MAX((P$1:P1<"")*(ROW(P$1:P1)))):O2,{1,2 ,3,4,5})),""))

Copy down as needed.

You can hide the result of the formula in cell P1 by setting the font
color to be the same as the background color.

Biff

"bill gras" wrote in message
...
I have cells O1 to O300 numbers in random sequences of no more than 10
I need to average the 5 highest numbers in each sequence eg:

O P
1 2 16 (result)
2 20
3 4
4 6
5 14
6 12
7 8
8 10
9 16
10 18
11 blank cell
12 15 9 (result)
13 3
14 6
15 12
16 9
17 blank cell
18 blank cell
19 4 23 (result)
20 16
21 20
22 40
23 8
24 11
25 28
26 blank cell
down to 300 rows, the sequence of numbers can be from 1 to 10 but never
more than 10
I got a function :
=IF(COUNT(O1:O10=10,AVERAGE(LARGE(O1:O10,{1,2,3,4 ,5})),"
") but that works only for 10 numbers and not for less

Can some one help please

bill
--
bill gras