AVERAGE Row of Numbers and Return Corresponding Numeric Label

[Sam via OfficeKB.com]

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

 

[Ron Rosenfeld]

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

 

[Sam via OfficeKB.com]

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

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

 

[Bob Phillips]

=INDEX(A20:K20,,MATCH(ROUND(AVERAGE(A21:K21),0),A21: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

 

[Sam via OfficeKB.com]

Hi Bob,

Thank you very much for your assistance. The Formulas work great.

=INDEX(A20:K20,,MATCH(ROUND(AVERAGE(A21:K21),0),A21: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

=INDEX(A20:K20,,MATCH(ROUND(AVERAGE(A21:K21),0),A21: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

 

[Ron Rosenfeld]

=INDEX(A20:K20,,MATCH(ROUND(AVERAGE(A21:K21),0),A21: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

I think there is a problem if the AVERAGE does not appear in A21:K21


--ron

 

[Ron Rosenfeld]

Hi Ron

Thanks for reply.


Rounded to nearest integer.


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

 

[Sam via OfficeKB.com]

Hi Ron,

Do you know of a workaround should the situation arise?

=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

=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

 

[Sam via OfficeKB.com]

Hi Ron,

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

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

 

[Ron Rosenfeld]

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

 

[Domenic]

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:K21>0,A21:K21)),0)

Hope this helps!

 

[Sam via OfficeKB.com]

Hi Ron,

Thank you for your time and help - the Formulas provide the required results -
great.

Cheers,
Sam

 

[Sam via 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

 

[Sam via 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]

Hi Ron,

Thank you for your time and help - the Formulas provide the required results -
great.

Cheers,
Sam

You're welcome. Glad it works for you.


--ron