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

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

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

=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

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

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

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

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

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

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

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!

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

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

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

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