average numbers in sequence

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

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(INDIRECT("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" <[email protected]> wrote in message
news:[email protected]...
>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

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" <[email protected]> wrote in message
news:%[email protected]...
> 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(INDIRECT("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" <[email protected]> wrote in message
> news:[email protected]...
>>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
>
>

Hi Biff,

I tried to understand and test your formula (in XL2000 and XL2003), but I
got a formula error message at the second argument of the LARGE function:
{1,2,3,4,5}

I looked up Help, and it doesn't mention an array as a second argument of
LARGE.

Would you explain it?

Thanks,
Stefi

"Biff" wrote:

> 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(INDIRECT("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" <[email protected]> wrote in message
> news:[email protected]...
> >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
>
>
>

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" <[email protected]> wrote in message
> news:%[email protected]...
> > 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(INDIRECT("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" <[email protected]> wrote in message
> > news:[email protected]...
> >>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
> >
> >
>
>
>

Bill
like Biff I have assumed there will never be less than 5 numbers in a
sequence
my initial solution also assumed you would put the formula in at the
correct point

=AVERAGE(LARGE(OFFSET(O1,0,0,MATCH(TRUE,ISBLANK(O1:O11),0)),{1,2,3,4,5}))

and like Biff's
> Enter this formula in P2 as an array using the key combo of
> CTRL,SHIFT,ENTER:

it uses the match to find the position of the first BLANK in a range of 11
cells (so there always is one)
then uses the offset to set the range you will average over

I had a quick try at using Biff's method to enclose this in an IF that
detected blank cells so you can simply copy down
If you are able to insert a blank row at the top of the data paste this
into P2 and copy down

=IF(AND(ISBLANK(O1),ISNUMBER(O2)),AVERAGE(LARGE(OFFSET(O2,0,0,MATCH(TRUE,ISBLANK(O2:O12),0)-1),{1,2,3,4,5})),"")

Also entered as Array

It feels clunky but does the job

hth RES

Hi Robert
Thank you for your imput it works great
regards bill
--
bill gras


"[email protected]" wrote:

> Bill
> like Biff I have assumed there will never be less than 5 numbers in a
> sequence
> my initial solution also assumed you would put the formula in at the
> correct point
>
> =AVERAGE(LARGE(OFFSET(O1,0,0,MATCH(TRUE,ISBLANK(O1:O11),0)),{1,2,3,4,5}))
>
> and like Biff's
> > Enter this formula in P2 as an array using the key combo of
> > CTRL,SHIFT,ENTER:
>
> it uses the match to find the position of the first BLANK in a range of 11
> cells (so there always is one)
> then uses the offset to set the range you will average over
>
> I had a quick try at using Biff's method to enclose this in an IF that
> detected blank cells so you can simply copy down
> If you are able to insert a blank row at the top of the data paste this
> into P2 and copy down
>
> =IF(AND(ISBLANK(O1),ISNUMBER(O2)),AVERAGE(LARGE(OFFSET(O2,0,0,MATCH(TRUE,ISBLANK(O2:O12),0)-1),{1,2,3,4,5})),"")
>
> Also entered as Array
>
> It feels clunky but does the job
>
> hth RES
>

Hi!

All I can say is that my test file works based on the sample you provided.
I'll send a copy if you'd like.

When posting it's a good idea to explain the problem EXACTLY as it is
appears in your file. Tell us EXACTLY what and where the ranges are instead
of using arbitrary examples.

This happens a lot!

Biff

"bill gras" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:%[email protected]...
>> > 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(INDIRECT("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" <[email protected]> wrote in message
>> > news:[email protected]...
>> >>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
>> >
>> >
>>
>>
>>

Hi!

> I looked up Help, and it doesn't mention an array as a second argument of
> LARGE.

Help doesn't mention a lot of things!

I can't explain why you would get an error message like that if the formula
was entered properly. Is the syntax correct?

I'll send you a sample file if you'd like. Just let me know where to send
it.

As a test, try this:

Enter some random numbers in A1:A10.

Then enter this formula:

=AVERAGE(LARGE(A1:A10,{1,2,3,4,5}))

Does that work?

Biff

"Stefi" <[email protected]> wrote in message
news:[email protected]...
> Hi Biff,
>
> I tried to understand and test your formula (in XL2000 and XL2003), but I
> got a formula error message at the second argument of the LARGE function:
> {1,2,3,4,5}
>
> I looked up Help, and it doesn't mention an array as a second argument of
> LARGE.
>
> Would you explain it?
>
> Thanks,
> Stefi
>
> "Biff" wrote:
>
>> 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(INDIRECT("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" <[email protected]> wrote in message
>> news:[email protected]...
>> >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
>>
>>
>>

bill gras wrote...
>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:
....

Enter the following *array* formula in cell P1.

P1:
=AVERAGE(LARGE(O1:INDEX(O2:O$65536,MATCH(TRUE,ISBLANK(O2:O$65536),0)),
ROW(INDIRECT("1:"&MIN(5,MATCH(TRUE,ISBLANK(O2:O$65536),0))))))

Then enter the following *array* formula in P2.

P2:
=IF(OR(ISNUMBER(O1),ISBLANK(O2)),"",
AVERAGE(LARGE(O2:INDEX(O3:O$65536,MATCH(TRUE,ISBLANK(O3:O$65536),0)),
ROW(INDIRECT("1:"&MIN(5,MATCH(TRUE,ISBLANK(O3:O$65536),0)))))))

Fill P2 down as far as needed.

No volatile function calls. Handles fewer than 5 values in each
sequence.

Hi Harlen
Thanks for your worksheet functions,it's perfect
regards bill
--
bill gras


"Harlan Grove" wrote:

> bill gras wrote...
> >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:
> ....
>
> Enter the following *array* formula in cell P1.
>
> P1:
> =AVERAGE(LARGE(O1:INDEX(O2:O$65536,MATCH(TRUE,ISBLANK(O2:O$65536),0)),
> ROW(INDIRECT("1:"&MIN(5,MATCH(TRUE,ISBLANK(O2:O$65536),0))))))
>
> Then enter the following *array* formula in P2.
>
> P2:
> =IF(OR(ISNUMBER(O1),ISBLANK(O2)),"",
> AVERAGE(LARGE(O2:INDEX(O3:O$65536,MATCH(TRUE,ISBLANK(O3:O$65536),0)),
> ROW(INDIRECT("1:"&MIN(5,MATCH(TRUE,ISBLANK(O3:O$65536),0)))))))
>
> Fill P2 down as far as needed.
>
> No volatile function calls. Handles fewer than 5 values in each
> sequence.
>
>

Hi Biff,

Thanks for the reply, I found out the cause of the problem, it belongs to
international issues of XL. I use a Hungarian version which uses semicolon as
list separator set in the Windows Control panel/Regional settings. When I try
formulas received from the community, I reset the Windows language to US
English and this involves changing the list separator to comma. It works
between function arguments, but it does NOT work between array elements! When
I set back the language to Hungarian and changed ALL separators to semicolon,
the formula worked! It's an inconsistency in producing national language
versions (it was absolutely needless to translate the function names and
basic syntax)!
I wonder if it is the same with other languages as well?

Regards,
Stefi


"Biff" wrote:

> Hi!
>
> > I looked up Help, and it doesn't mention an array as a second argument of
> > LARGE.
>
> Help doesn't mention a lot of things!
>
> I can't explain why you would get an error message like that if the formula
> was entered properly. Is the syntax correct?
>
> I'll send you a sample file if you'd like. Just let me know where to send
> it.
>
> As a test, try this:
>
> Enter some random numbers in A1:A10.
>
> Then enter this formula:
>
> =AVERAGE(LARGE(A1:A10,{1,2,3,4,5}))
>
> Does that work?
>
> Biff
>
> "Stefi" <[email protected]> wrote in message
> news:[email protected]...
> > Hi Biff,
> >
> > I tried to understand and test your formula (in XL2000 and XL2003), but I
> > got a formula error message at the second argument of the LARGE function:
> > {1,2,3,4,5}
> >
> > I looked up Help, and it doesn't mention an array as a second argument of
> > LARGE.
> >
> > Would you explain it?
> >
> > Thanks,
> > Stefi
> >
> > "Biff" wrote:
> >
> >> 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(INDIRECT("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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >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
> >>
> >>
> >>
>
>
>