How does 1 value affect the average? in Excel Worksheet Functions  
 |  Edit my Profile  |  Help
 
     
   
 
 
 
Bruce 10/26/2007 11:07 AM PST
  Question
  I have a list of hundreds of numbers.
I have averaged the numbers, and the average is about 100.

I want to see what impact each value has on the average.

To be overly simple. If I had a list of 1, 2, 3, 4, 5, 100. The average is
~19. Without the 100, the average is 3. So the 100 has a greater impact on
the average than do the other values.

How do I express that?
 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Pete_UK 10/26/2007 11:20 AM PST
  Answer
  If you take the total of those numbers (115) and express 100 as a
percentage of the total you get 87%, i.e. the number 100 is
contributing 87% to the total, whereas the number 3, for example, is
only 2.6%. So, the relative "worth" or weight of a number can be
expressed as:

=A1/SUM(A$1:A$100)

formatted as a percentage and copied down.

Hope this helps.

Pete

On Oct 26, 7:08 pm, Bruce <Br...@discussions.microsoft.com> wrote:
> I have a list of hundreds of numbers.
> I have averaged the numbers, and the average is about 100.
>
> I want to see what impact each value has on the average.
>
> To be overly simple. If I had a list of 1, 2, 3, 4, 5, 100. The average is
> ~19. Without the 100, the average is 3. So the 100 has a greater impact on
> the average than do the other values.
>
> How do I express that?


 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Bruce 10/26/2007 12:11 PM PST
   
  Now that I see it, it's simple. Thanks for getting me there.

"Pete_UK" wrote:

> If you take the total of those numbers (115) and express 100 as a
> percentage of the total you get 87%, i.e. the number 100 is
> contributing 87% to the total, whereas the number 3, for example, is
> only 2.6%. So, the relative "worth" or weight of a number can be
> expressed as:
>
> =A1/SUM(A$1:A$100)
>
> formatted as a percentage and copied down.
>
> Hope this helps.
>
> Pete
>
> On Oct 26, 7:08 pm, Bruce <Br...@discussions.microsoft.com> wrote:
> > I have a list of hundreds of numbers.
> > I have averaged the numbers, and the average is about 100.
> >
> > I want to see what impact each value has on the average.
> >
> > To be overly simple. If I had a list of 1, 2, 3, 4, 5, 100. The average is
> > ~19. Without the 100, the average is 3. So the 100 has a greater impact on
> > the average than do the other values.
> >
> > How do I express that?
>
>
>
 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Pete_UK 10/26/2007 12:44 PM PST
   
  You're welcome, Bruce - thanks for feeding back.

Pete

On Oct 26, 8:12 pm, Bruce <Br...@discussions.microsoft.com> wrote:
> Now that I see it, it's simple. Thanks for getting me there.
>
>
>
> "Pete_UK" wrote:
> > If you take the total of those numbers (115) and express 100 as a
> > percentage of the total you get 87%, i.e. the number 100 is
> > contributing 87% to the total, whereas the number 3, for example, is
> > only 2.6%. So, the relative "worth" or weight of a number can be
> > expressed as:
>
> > =A1/SUM(A$1:A$100)
>
> > formatted as a percentage and copied down.
>
> > Hope this helps.
>
> > Pete
>
> > On Oct 26, 7:08 pm, Bruce <Br...@discussions.microsoft.com> wrote:
> > > I have a list of hundreds of numbers.
> > > I have averaged the numbers, and the average is about 100.
>
> > > I want to see what impact each value has on the average.
>
> > > To be overly simple. If I had a list of 1, 2, 3, 4, 5, 100. The average is
> > > ~19. Without the 100, the average is 3. So the 100 has a greater impact on
> > > the average than do the other values.
>
> > > How do I express that?- Hide quoted text -
>
> - Show quoted text -


 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
  Return to Microsoft Communities  Notify me of replies