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.

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" wrote in message
77.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.

Try...

=AVERAGE(IF(A1:A80,A1:A8))

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

Hope this helps!

In article 35,
Marc Fleury 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.

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 wrote:

Try...

=AVERAGE(IF(A1:A80,A1:A8))

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

Hope this helps!

In article 35,
Marc Fleury 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,

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

Thanks again.

In article ,
Myrna Larson 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 wrote:

Try...

=AVERAGE(IF(A1:A80,A1:A8))

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

Hope this helps!

In article 35,
Marc Fleury 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.