Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Sam via OfficeKB.com
 
Posts: n/a
Default 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   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

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   Report Post  
Sam via OfficeKB.com
 
Posts: n/a
Default

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   Report Post  
Bob Phillips
 
Posts: n/a
Default

=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   Report Post  
Sam via OfficeKB.com
 
Posts: n/a
Default

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   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

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   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

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   Report Post  
Sam via OfficeKB.com
 
Posts: n/a
Default

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   Report Post  
Sam via OfficeKB.com
 
Posts: n/a
Default

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   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

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   Report Post  
Domenic
 
Posts: n/a
Default

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   Report Post  
Sam via OfficeKB.com
 
Posts: n/a
Default

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   Report Post  
Sam via OfficeKB.com
 
Posts: n/a
Default

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   Report Post  
Sam via OfficeKB.com
 
Posts: n/a
Default

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   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
In Excl how can I return letters to the columns I now have numbers Column Idenity Excel Discussion (Misc queries) 1 August 18th 05 08:25 PM
Return a digit in a string of numbers W M Excel Discussion (Misc queries) 5 May 11th 05 06:51 PM
How to compare 3 numbers and return value basic Excel Worksheet Functions 6 April 8th 05 05:15 AM
Match Last Occurrence of two numbers and Return Date Sam via OfficeKB.com Excel Worksheet Functions 6 April 5th 05 12:40 PM
Match Last Occurrence of two numbers and Count to Previous Occurence Sam via OfficeKB.com Excel Worksheet Functions 33 April 4th 05 02:17 PM


All times are GMT +1. The time now is 10:11 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"