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"

 

[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)

 

[Domenic]

Try...

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

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

Hope this helps!

 

[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.

 

[Domenic]

Myrna,

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

Thanks again.