Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
average numbers in sequence
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 |
#3
|
|||
|
|||
average numbers in sequence
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 |
#4
|
|||
|
|||
average numbers in sequence
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(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 |
#5
|
|||
|
|||
average numbers in sequence
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 |
#6
|
|||
|
|||
average numbers in sequence
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(OF FSET(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 |
#7
|
|||
|
|||
average numbers in sequence
Hi Robert
Thank you for your imput it works great regards bill -- bill gras " 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(OF FSET(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 |
#8
|
|||
|
|||
average numbers in sequence
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" wrote in message ... 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 |
#9
|
|||
|
|||
average numbers in sequence
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" wrote in message ... 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(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 |
#10
|
|||
|
|||
average numbers in sequence
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,ISBL ANK(O2:O$65536),0)), ROW(INDIRECT("1:"&MIN(5,MATCH(TRUE,ISBLANK(O2:O$65 536),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,ISBLA NK(O3:O$65536),0)), ROW(INDIRECT("1:"&MIN(5,MATCH(TRUE,ISBLANK(O3:O$65 536),0))))))) Fill P2 down as far as needed. No volatile function calls. Handles fewer than 5 values in each sequence. |
#11
|
|||
|
|||
average numbers in 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,ISBL ANK(O2:O$65536),0)), ROW(INDIRECT("1:"&MIN(5,MATCH(TRUE,ISBLANK(O2:O$65 536),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,ISBLA NK(O3:O$65536),0)), ROW(INDIRECT("1:"&MIN(5,MATCH(TRUE,ISBLANK(O3:O$65 536),0))))))) Fill P2 down as far as needed. No volatile function calls. Handles fewer than 5 values in each sequence. |
#12
|
|||
|
|||
average numbers in 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" wrote in message ... 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(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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
AVERAGE Row of Numbers and Return Corresponding Numeric Label | Excel Worksheet Functions | |||
average of kth largest numbers in an array of n numbers | Excel Worksheet Functions | |||
Average of numbers within a range meeting certain criteria | Excel Worksheet Functions | |||
Finding numbers missing from a sequence | Excel Discussion (Misc queries) | |||
Average of numbers in column between to other numbers | Excel Discussion (Misc queries) |