Home |
Search |
Today's Posts |
#1
|
|||
|
|||
AVERAGE Row of Numbers and Return Corresponding Numeric Label
Hi All,
I have one Row that houses numbers 80-90 in seperate cells (11 columns A20: K20) - these are my Numeric Labels. I then have several other Rows that span the same number of columns as the Numeric Labels (A21:K100) and house Numeric Values that relate to the Numeric Labels. I would like to AVERAGE the Numeric Values in each Row and have a Numeric Label (value) that corresponds to the calculated average returned as the result. Example: Sample Data A20:K20 (Numeric Label) 80 81 82 83 84 85 86 87 88 89 90 A21:K21 (Numeric Value) 07 06 05 03 09 09 00 02 04 12 10 Based on the above data - the Expected Result is 81 Average = 6; Return Numeric Label = 81 The leading zeros shown in A21:K21 is for alignment purposes only. I would appreciate two formulas: 1) Includes zeros to be averaged 2) Excludes zeros from being averaged I have tried variations on this =INDEX($A$20:$KJ$20,MATCH(AVERAGE($A21:$K21), $A21:$K21,0)) but receive the #N/A error message. Thanks Sam -- Message posted via http://www.officekb.com |
#2
|
|||
|
|||
On Mon, 19 Sep 2005 16:19:41 GMT, "Sam via OfficeKB.com"
wrote: Hi All, I have one Row that houses numbers 80-90 in seperate cells (11 columns A20: K20) - these are my Numeric Labels. I then have several other Rows that span the same number of columns as the Numeric Labels (A21:K100) and house Numeric Values that relate to the Numeric Labels. I would like to AVERAGE the Numeric Values in each Row and have a Numeric Label (value) that corresponds to the calculated average returned as the result. Example: Sample Data A20:K20 (Numeric Label) 80 81 82 83 84 85 86 87 88 89 90 A21:K21 (Numeric Value) 07 06 05 03 09 09 00 02 04 12 10 Based on the above data - the Expected Result is 81 Average = 6; Return Numeric Label = 81 The leading zeros shown in A21:K21 is for alignment purposes only. I would appreciate two formulas: 1) Includes zeros to be averaged 2) Excludes zeros from being averaged I have tried variations on this =INDEX($A$20:$KJ$20,MATCH(AVERAGE($A21:$K21), $A21:$K21,0)) but receive the #N/A error message. Thanks Sam Need more information. 1. The Average of your values is not 6, but rather 6.090909091. How, exactly, do you want to process non-integer averages? 2. How do you decide which Label to return if you have duplicate Values? In other words, if the processed average is 9, how do we tell whether to return 84, or 85? --ron |
#3
|
|||
|
|||
Hi Ron
Thanks for reply. 1. The Average of your values is not 6, but rather 6.090909091. How, exactly, do you want to process non-integer averages? Rounded to nearest integer. 2. How do you decide which Label to return if you have duplicate Values? In other words, if the processed average is 9, how do we tell whether to return 84, or 85? The first matched Label. Cheers, Sam Ron Rosenfeld wrote: Hi All, [quoted text clipped - 27 lines] Thanks Sam Need more information. 1. The Average of your values is not 6, but rather 6.090909091. How, exactly, do you want to process non-integer averages? 2. How do you decide which Label to return if you have duplicate Values? In other words, if the processed average is 9, how do we tell whether to return 84, or 85? --ron -- Message posted via http://www.officekb.com |
#4
|
|||
|
|||
=INDEX(A20:K20,,MATCH(ROUND(AVERAGE(A21:K21),0),A2 1:K21,0))
and =INDEX(A20:K20,,MATCH(ROUND(AVERAGE(IF(A21:K21<0, A21:K21)),0),A21:K21,0)) the second is an array formula, so you need to commit with Ctrl-Shift-Enter -- HTH Bob Phillips "Sam via OfficeKB.com" wrote in message ... Hi All, I have one Row that houses numbers 80-90 in seperate cells (11 columns A20: K20) - these are my Numeric Labels. I then have several other Rows that span the same number of columns as the Numeric Labels (A21:K100) and house Numeric Values that relate to the Numeric Labels. I would like to AVERAGE the Numeric Values in each Row and have a Numeric Label (value) that corresponds to the calculated average returned as the result. Example: Sample Data A20:K20 (Numeric Label) 80 81 82 83 84 85 86 87 88 89 90 A21:K21 (Numeric Value) 07 06 05 03 09 09 00 02 04 12 10 Based on the above data - the Expected Result is 81 Average = 6; Return Numeric Label = 81 The leading zeros shown in A21:K21 is for alignment purposes only. I would appreciate two formulas: 1) Includes zeros to be averaged 2) Excludes zeros from being averaged I have tried variations on this =INDEX($A$20:$KJ$20,MATCH(AVERAGE($A21:$K21), $A21:$K21,0)) but receive the #N/A error message. Thanks Sam -- Message posted via http://www.officekb.com |
#5
|
|||
|
|||
Hi Bob,
Thank you very much for your assistance. The Formulas work great. Bob Phillips wrote: =INDEX(A20:K20,,MATCH(ROUND(AVERAGE(A21:K21),0),A 21:K21,0)) and =INDEX(A20:K20,,MATCH(ROUND(AVERAGE(IF(A21:K21<0 ,A21:K21)),0),A21:K21,0)) the second is an array formula, so you need to commit with Ctrl-Shift-Enter Cheers, Sam Bob Phillips wrote: =INDEX(A20:K20,,MATCH(ROUND(AVERAGE(A21:K21),0),A 21:K21,0)) and =INDEX(A20:K20,,MATCH(ROUND(AVERAGE(IF(A21:K21<0 ,A21:K21)),0),A21:K21,0)) the second is an array formula, so you need to commit with Ctrl-Shift-Enter Hi All, [quoted text clipped - 27 lines] Thanks Sam -- Message posted via http://www.officekb.com |
#6
|
|||
|
|||
On Mon, 19 Sep 2005 18:55:14 +0100, "Bob Phillips"
wrote: =INDEX(A20:K20,,MATCH(ROUND(AVERAGE(A21:K21),0),A 21:K21,0)) and =INDEX(A20:K20,,MATCH(ROUND(AVERAGE(IF(A21:K21<0 ,A21:K21)),0),A21:K21,0)) the second is an array formula, so you need to commit with Ctrl-Shift-Enter -- HTH I think there is a problem if the AVERAGE does not appear in A21:K21 --ron |
#7
|
|||
|
|||
On Mon, 19 Sep 2005 17:52:26 GMT, "Sam via OfficeKB.com"
wrote: Hi Ron Thanks for reply. 1. The Average of your values is not 6, but rather 6.090909091. How, exactly, do you want to process non-integer averages? Rounded to nearest integer. 2. How do you decide which Label to return if you have duplicate Values? In other words, if the processed average is 9, how do we tell whether to return 84, or 85? The first matched Label. Cheers, Sam OK, but those answers lead to another problem. What do you want to do if the ROUNDED average is not exactly equal to any particular number in VALUES? --ron |
#8
|
|||
|
|||
Hi Ron,
Do you know of a workaround should the situation arise? Ron Rosenfeld wrote: =INDEX(A20:K20,,MATCH(ROUND(AVERAGE(A21:K21),0), A21:K21,0)) [quoted text clipped - 3 lines] the second is an array formula, so you need to commit with Ctrl-Shift-Enter I think there is a problem if the AVERAGE does not appear in A21:K21 Cheers, Sam Ron Rosenfeld wrote: =INDEX(A20:K20,,MATCH(ROUND(AVERAGE(A21:K21),0), A21:K21,0)) [quoted text clipped - 3 lines] the second is an array formula, so you need to commit with Ctrl-Shift-Enter I think there is a problem if the AVERAGE does not appear in A21:K21 --ron -- Message posted via http://www.officekb.com |
#9
|
|||
|
|||
Hi Ron,
Ron Rosenfeld wrote: What do you want to do if the ROUNDED average is not exactly equal to any particular number in VALUES? Use the next highest available average number - if the average is 10 and that does not exist in the Row, then go one number above = 11. Cheers, Sam Ron Rosenfeld wrote: Hi Ron [quoted text clipped - 13 lines] Cheers, Sam OK, but those answers lead to another problem. What do you want to do if the ROUNDED average is not exactly equal to any particular number in VALUES? --ron -- Message posted via http://www.officekb.com |
#10
|
|||
|
|||
On Mon, 19 Sep 2005 20:20:40 GMT, "Sam via OfficeKB.com"
wrote: Hi Ron, Do you know of a workaround should the situation arise? This **array** function will look for the Value that is *closest* to the Average, and then match the corresponding label. I have used NAME'd ranges for Labels and Values, but you can use cell references if you prefer. =INDEX(Labels,,MATCH(MIN(ABS(Values-AVERAGE( Values))),ABS(Values-AVERAGE(Values)),0)) If you want to exclude 0's, then for the AVERAGE function substitute SUM(Values)/COUNTIF(Values,"<0") So you wind up with: =INDEX(Labels,,MATCH(MIN(ABS(Values-SUM(Values)/ COUNTIF(Values,"<0"))),ABS(Values-SUM( Values)/COUNTIF(Values,"<0")),0)) Remember, with an array formula you must hold down <ctrl<shift while hitting <enter. Excel will place braces {...} around the formula. I did not bother to ROUND the AVERAGE, but you could if you need to. --ron |
#11
|
|||
|
|||
In article ,
"Sam via OfficeKB.com" wrote: Use the next highest available average number - if the average is 10 and that does not exist in the Row, then go one number above = 11. That begs the question...what if 11 doesn't exist? :) Assuming that you want to continue to the next higher number until you find a match, try the following... L21, copied down: =ROUND(AVERAGE(A21:K21),0) M21, copied down: =INDEX($A$20:$K$20,MATCH(MIN(IF(A21:K21=L21,ABS(A 21:K21-L21))),IF(A21:K2 1=L21,ABS(A21:K21-L21)),0)) ....confirmed with CONTROL+SHIFT+ENTER. To exclude zeros from your average, you can use the following formula... =ROUND(AVERAGE(IF(A21:K210,A21:K21)),0) Hope this helps! |
#12
|
|||
|
|||
Hi Ron,
Thank you for your time and help - the Formulas provide the required results - great. Cheers, Sam Ron Rosenfeld wrote: Hi Ron, Do you know of a workaround should the situation arise? This **array** function will look for the Value that is *closest* to the Average, and then match the corresponding label. I have used NAME'd ranges for Labels and Values, but you can use cell references if you prefer. =INDEX(Labels,,MATCH(MIN(ABS(Values-AVERAGE( Values))),ABS(Values-AVERAGE(Values)),0)) If you want to exclude 0's, then for the AVERAGE function substitute SUM(Values)/COUNTIF(Values,"<0") So you wind up with: =INDEX(Labels,,MATCH(MIN(ABS(Values-SUM(Values)/ COUNTIF(Values,"<0"))),ABS(Values-SUM( Values)/COUNTIF(Values,"<0")),0)) Remember, with an array formula you must hold down <ctrl<shift while hitting <enter. Excel will place braces {...} around the formula. I did not bother to ROUND the AVERAGE, but you could if you need to. --ron -- Message posted via http://www.officekb.com |
#13
|
|||
|
|||
Hi Domenic,
Thank you for providing a flexible solution to my problem; as you said, there may be occassions when the next highest average does not appear in the Row. The Formula does provide the required results. Cheers Sam Domenic wrote: Use the next highest available average number - if the average is 10 and that does not exist in the Row, then go one number above = 11. That begs the question...what if 11 doesn't exist? :) Assuming that you want to continue to the next higher number until you find a match, try the following... L21, copied down: =ROUND(AVERAGE(A21:K21),0) M21, copied down: =INDEX($A$20:$K$20,MATCH(MIN(IF(A21:K21=L21,ABS( A21:K21-L21))),IF(A21:K2 1=L21,ABS(A21:K21-L21)),0)) ...confirmed with CONTROL+SHIFT+ENTER. To exclude zeros from your average, you can use the following formula... =ROUND(AVERAGE(IF(A21:K210,A21:K21)),0) Hope this helps! -- Message posted via http://www.officekb.com |
#14
|
|||
|
|||
Hi -
Bob, Ron, Domenic, Thank you all for sharing your knowledge and expertise; not to mention your forward thinking in providing such a flexible solution. Cheers, Sam Ron Rosenfeld wrote: Hi Ron, Do you know of a workaround should the situation arise? This **array** function will look for the Value that is *closest* to the Average, and then match the corresponding label. I have used NAME'd ranges for Labels and Values, but you can use cell references if you prefer. =INDEX(Labels,,MATCH(MIN(ABS(Values-AVERAGE( Values))),ABS(Values-AVERAGE(Values)),0)) If you want to exclude 0's, then for the AVERAGE function substitute SUM(Values)/COUNTIF(Values,"<0") So you wind up with: =INDEX(Labels,,MATCH(MIN(ABS(Values-SUM(Values)/ COUNTIF(Values,"<0"))),ABS(Values-SUM( Values)/COUNTIF(Values,"<0")),0)) Remember, with an array formula you must hold down <ctrl<shift while hitting <enter. Excel will place braces {...} around the formula. I did not bother to ROUND the AVERAGE, but you could if you need to. --ron -- Message posted via http://www.officekb.com |
#15
|
|||
|
|||
On Mon, 19 Sep 2005 23:32:36 GMT, "Sam via OfficeKB.com"
wrote: Hi Ron, Thank you for your time and help - the Formulas provide the required results - great. Cheers, Sam Ron Rosenfeld wrote: Hi Ron, Do you know of a workaround should the situation arise? This **array** function will look for the Value that is *closest* to the Average, and then match the corresponding label. I have used NAME'd ranges for Labels and Values, but you can use cell references if you prefer. =INDEX(Labels,,MATCH(MIN(ABS(Values-AVERAGE( Values))),ABS(Values-AVERAGE(Values)),0)) If you want to exclude 0's, then for the AVERAGE function substitute SUM(Values)/COUNTIF(Values,"<0") So you wind up with: =INDEX(Labels,,MATCH(MIN(ABS(Values-SUM(Values)/ COUNTIF(Values,"<0"))),ABS(Values-SUM( Values)/COUNTIF(Values,"<0")),0)) Remember, with an array formula you must hold down <ctrl<shift while hitting <enter. Excel will place braces {...} around the formula. I did not bother to ROUND the AVERAGE, but you could if you need to. --ron You're welcome. Glad it works for you. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
In Excl how can I return letters to the columns I now have numbers | Excel Discussion (Misc queries) | |||
Return a digit in a string of numbers | Excel Discussion (Misc queries) | |||
How to compare 3 numbers and return value | Excel Worksheet Functions | |||
Match Last Occurrence of two numbers and Return Date | Excel Worksheet Functions | |||
Match Last Occurrence of two numbers and Count to Previous Occurence | Excel Worksheet Functions |