Excel function to calculate deviation from a fixed value

[PaulMTC]

Hi all,

i'm trying to find a function in excel that will calculate the average deviation/variance of a data set from a fixed value (rather than from the mean like standard deviation etc).

e.g. if i have a data set (1,-15,5,9,21,-3,12,10,1) i want to determine the average deviation from "zero" rather than the "mean" of the data set.

can anyone help with this?

thanks,

paul.

[XOR LX]

Then by definition you are simply calculating:

=SQRT(AVERAGE(POWER(YourRange,2)))

which requires entering as an array formula**.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

[PaulMTC]

that's brilliant thanks for the quick reply XOR.

i tried that and it worked. the only thing is that i am applying this to a range that values will build up and be populated over time. when i use STDEV it ignores "nulls" ie. cells where no value exists yet. however the solution you have provided treats these "nulls" as zeros.

is there anyway to modify your function to ignore the "nulls"?


thanks again.


paul.

[XOR LX]

Sure:

=SQRT(AVERAGE(IF(ISNUMBER(YourRange),POWER(YourRange,2))))

Cheers

[PaulMTC]

brilliant thanks!

[XOR LX]

You're welcome!

Cheers!

[PaulMTC]

as a matter of interest, what is the reason behind squaring the values before averaging and then square rooting?

ie. compared to just turning all the values in the range positive and averaging that?

[Tony Valko]

compared to just turning all the values in the range positive and averaging that?

I thought that's what you wanted to do but that formula gives a different result than XOR's formula.

Both formulas are array entered**:

=AVERAGE(ABS(A1:A9)) returns 8.55555555555556

=SQRT(AVERAGE(POWER(A1:A9,2))) returns 10.682280239308

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[PaulMTC]

thanks tony. yes i just wanted the average of the distance away from zero - not squaring the distance, then averaging, then square rooting.

..... i just wondered if XOR's way had other merits and if so what are they?

[XOR LX]

When you mentioned "deviation" in your original post, I just assumed (perhaps wrongly) that you wanted a variation on standard deviation.

After all, your argument could be extended to that definition as well, as indeed it clearly has been:

http://stats.stackexchange.com/quest...standard-devia

I'll let you decide whether squaring offers you a "better" means of representing "deviation" for your set-up.

Regards

[PaulMTC]

that's a really interesting post you've linked to XOR and answers my question. i don't need to "magnify" values the further they are from zero so "absolute" rather than "squared" differences is better for the situation i'm dealing with. thanks very much again for your time and help (XOR and tony).

[XOR LX]

Sure. Sounds like you're pretty sure what best fits your situation, so that's good.

And you're welcome!

Cheers

[Tony Valko]

If you need to exclude empty cells...

=AVERAGE(IF(ISNUMBER(A1:A9),ABS(A1:A9)))

Or, if the data will always be numbers (no text)...

=AVERAGE(IF(A1:A9<>"",ABS(A1:A9)))

Both are still array entered!