Absolute average

[Marc Fleury]

I'm putting together a sheet that ranks a bunch of gamers based on their
stats. The ranking will be done once per week. After each ranking, I'm
listing how much the rank changed since the previous ranking. So, I have a
column of numbers which include both positive and negatives. The Sum and
the Average of this column is always going to be zero, but I want to show
the average change in absolute terms. I.e. the average player moved 2.5
ranks this week (whether up or down the ranks).

I'm thinking AVERAGE(ABS(A:A)) but it doesn't calculate correctly. For this
sample data:

1
-1
2
-2
3
-3
4
-4

That formula calculates "4" but it should be "2.5"


--
Marc.

[N Harkawat]

Use
=average(abs(a1:a1000))
array entered (by press ctrl+shift+enter instead of just enter)

Or
=AVERAGE(IF(ISNUMBER(A1:A1000),ABS(A1:A1000)))
to eliminate any values that are not numbers in the range (again array
entered)


"Marc Fleury" <marcfleury@sympatico.ca> wrote in message
news:Xns962A59F6D731Amarcfleurysympaticoc@207.35.177.135...
> I'm putting together a sheet that ranks a bunch of gamers based on their
> stats. The ranking will be done once per week. After each ranking, I'm
> listing how much the rank changed since the previous ranking. So, I have a
> column of numbers which include both positive and negatives. The Sum and
> the Average of this column is always going to be zero, but I want to show
> the average change in absolute terms. I.e. the average player moved 2.5
> ranks this week (whether up or down the ranks).
>
> I'm thinking AVERAGE(ABS(A:A)) but it doesn't calculate correctly. For
> this
> sample data:
>
> 1
> -1
> 2
> -2
> 3
> -3
> 4
> -4
>
> That formula calculates "4" but it should be "2.5"
>
>
> --
> Marc.

[Domenic]

Try...

=AVERAGE(IF(A1:A8>0,A1:A8))

.....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article <Xns962A59F6D731Amarcfleurysympaticoc@207.35.177.135>,
Marc Fleury <marcfleury@sympatico.ca> wrote:

> I'm putting together a sheet that ranks a bunch of gamers based on their
> stats. The ranking will be done once per week. After each ranking, I'm
> listing how much the rank changed since the previous ranking. So, I have a
> column of numbers which include both positive and negatives. The Sum and
> the Average of this column is always going to be zero, but I want to show
> the average change in absolute terms. I.e. the average player moved 2.5
> ranks this week (whether up or down the ranks).
>
> I'm thinking AVERAGE(ABS(A:A)) but it doesn't calculate correctly. For this
> sample data:
>
> 1
> -1
> 2
> -2
> 3
> -3
> 4
> -4
>
> That formula calculates "4" but it should be "2.5"
>
>
> --
> Marc.

[Myrna Larson]

That is averaging only those people who moved up. He wants to include up or
down, or (presumably) unchanged. i.e. if nobody's rank changed, they values
are all 0, and your formula will give a #DIV/0 error.


On Thu, 31 Mar 2005 09:18:37 -0500, Domenic <domenic22@sympatico.ca> wrote:

>Try...
>
>=AVERAGE(IF(A1:A8>0,A1:A8))
>
>....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.
>
>Hope this helps!
>
>In article <Xns962A59F6D731Amarcfleurysympaticoc@207.35.177.135>,
> Marc Fleury <marcfleury@sympatico.ca> wrote:
>
>> I'm putting together a sheet that ranks a bunch of gamers based on their
>> stats. The ranking will be done once per week. After each ranking, I'm
>> listing how much the rank changed since the previous ranking. So, I have a
>> column of numbers which include both positive and negatives. The Sum and
>> the Average of this column is always going to be zero, but I want to show
>> the average change in absolute terms. I.e. the average player moved 2.5
>> ranks this week (whether up or down the ranks).
>>
>> I'm thinking AVERAGE(ABS(A:A)) but it doesn't calculate correctly. For this
>> sample data:
>>
>> 1
>> -1
>> 2
>> -2
>> 3
>> -3
>> 4
>> -4
>>
>> That formula calculates "4" but it should be "2.5"
>>
>>
>> --
>> Marc.

[Domenic]

Myrna,

Thanks for pointing that out. I always appreciate any feedback,
especially when I royally mess up as I did now.

Thanks again.

In article <0dgo419gqli456tsog3oinbjdkfimjgset@4ax.com>,
Myrna Larson <anonymous@discussions.microsoft.com> wrote:

> That is averaging only those people who moved up. He wants to include up or
> down, or (presumably) unchanged. i.e. if nobody's rank changed, they values
> are all 0, and your formula will give a #DIV/0 error.
>
>
> On Thu, 31 Mar 2005 09:18:37 -0500, Domenic <domenic22@sympatico.ca> wrote:
>
> >Try...
> >
> >=AVERAGE(IF(A1:A8>0,A1:A8))
> >
> >....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.
> >
> >Hope this helps!
> >
> >In article <Xns962A59F6D731Amarcfleurysympaticoc@207.35.177.135>,
> > Marc Fleury <marcfleury@sympatico.ca> wrote:
> >
> >> I'm putting together a sheet that ranks a bunch of gamers based on their
> >> stats. The ranking will be done once per week. After each ranking, I'm
> >> listing how much the rank changed since the previous ranking. So, I have a
> >> column of numbers which include both positive and negatives. The Sum and
> >> the Average of this column is always going to be zero, but I want to show
> >> the average change in absolute terms. I.e. the average player moved 2.5
> >> ranks this week (whether up or down the ranks).
> >>
> >> I'm thinking AVERAGE(ABS(A:A)) but it doesn't calculate correctly. For this
> >> sample data:
> >>
> >> 1
> >> -1
> >> 2
> >> -2
> >> 3
> >> -3
> >> 4
> >> -4
> >>
> >> That formula calculates "4" but it should be "2.5"
> >>
> >>
> >> --
> >> Marc.