average numbers in sequence

[bill gras]

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

 

[Biff]

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$11<>"")*(ROW(P$11)))):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

 

[Biff]

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

 

[Stefi]

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

 

[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

 

[Robert_Steel]

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

 

[bill gras]

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

 

[Biff]

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

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

 

[Biff]

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

 

[Harlan Grove]

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.

 

[bill gras]

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

 

[Stefi]

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