Incorrect result using AVEDEV formula

[Don F]

I am getting a result that I beleive is incorrect using the AVEDEV formula.
My data is .492, .580, .589, .735, .803, .819, 1.084, 1.413, 1.423 & 1.613.
AVEDEV is giving me a result of 34.3%, but when I do the calculation by hand,
I get 31.5%. Does anyone have an idea what I might be doing wrong?

 

[Bernie Deitrick]

Don,

AVEDEV returns the Average of the absolute values of the differences between
the values and the average of the values. Using standard functions
(actually, an array formula entered using Ctrl-Shift-Enter) this is the same
as AVEDEV

=AVERAGE(ABS(A2:A11-AVERAGE(A2:A11)))

I get the same value as you for both calcs (34.3%).

Unless you post your manual method, we cannot be sure what you are doing
wrong.

HTH,
Bernie
MS Excel MVP

 

[David Biddulph]

You haven't explained how you did your calculation by hand, so it's not easy
for us to tell you what you did wrong, Don.

The average of your numbers is 0.9551
The absolute values of the differences between your initial values and the
average are:
0.4631
0.3751
0.3661
0.2201
0.1521
0.1361
0.1289
0.4579
0.4679
0.6579

The sum of these values is 3.4252

Divide this by 10, and you get 0.34252, which is what Excel gives for
AVEDEV.

Perhaps you can explain how you got your 31.5%, and what the intermediate
values in your calculation were?

 

[Joe User]

I am getting a result that I beleive is incorrect using the AVEDEV
formula.
My data is .492, .580, .589, .735, .803, .819, 1.084, 1.413, 1.423 &
1.613.
AVEDEV is giving me a result of 34.3%, but when I do the calculation by
hand,
I get 31.5%. Does anyone have an idea what I might be doing wrong?

Nope, since you neglect to explain your calculation "by hand". (Klunk!)

When I enter those constants as you wrote them, my results with AVEDEV
matches my "manual" results. "Manually", I compute AVERAGE(A1:A10) in C1,
then ABS(A1-$C$1) in B1 and copy into B2:B10, then SUM(B1:B10)/10. The
result is about 0.3425.

What do you mean by "by hand"? If you copied those numbers by rewriting
them, perhaps you are a little dyslexic. For example, I get about 0.315
under the following conditions (at least): (a) A3 is 0.598, and A8 is
1.431or A10 is 1.631; and (b) A6 is 0.891, and A2 is 0.850 or A3 is 0.859 or
A9 is 1.243, if I transcribed my notes correctly ;-).

Another frequent source of disparity between calculations by Excel and "by
hand' is when you copy numbers by rewriting the displayed values instead of
using the actual cell value (or copy-and-pasting the value displayed with 15
significant digits, which is "close"). For example, the displayed number
0.492 might actually be any value between about 0.4915 and
0.492499999999999. I don't have the patience to experiment with all those
combinations ;-).

Finally, note that AVEDEV results in a number of the same type of units as
its parameters. So with those numbers above, AVEDEV results in 0.3425. It
is not 34.25% unless your numbers are percentages themselves, expressed as
decimal fractions.

In contrast, there is a statistic that is expressed as a percentage, namely
AVEDEV(A1:A10)/AVERAGE(A1:A10). I call that the "relative mean deviation".

 

[Don F]

Bernie, David, & Joe:

Thank you for your replies to my post. I discovered my error, it was due to
me misreading MEDIAN in the excel help for the formula instead of Mean.
Sorry for not providing sufficient information in my original post, I will be
more thorough next time.

Don