ExcelBanter - View Single Post

Joe User[_2_]

"Don F" <Don wrote:
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".