=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