Function for of sum of square of mean deviation

[arrun]

I am looking for a excel function (not UDF, this I can do for myself if really there is no inbuilt function) to calculate "sum of square of mean deviation". For example let have 3 numbers : 1,2,3 then by definition sum of square of mean deviation is : (1-2)^2 + (2-2)^2 + (3-2)^2. I am aware of sumsq() function but it is not that what I am looking for.

[anandbohra]

I suppose u r asking for standard deviation
the formula is =stdev(select range) or
=stdevp(select range) ' this one is for population standard deviation


Regards,

Anand M. Bohra

[arrun]

Nopes, I am aware of that stdev formula. However I want generalization so that instead of mean, i can put any number. What? in case I want to do : (1-A1)^2 + (2-A1)^2 + (3-A1)^2 ? instead of mean? (A1 is 1st cell)

[Bob Phillips]

Is this what you want?

=SUMPRODUCT((ROW(INDIRECT("1:3"))-A1)^2)

[arrun]

Hi XLD, instead of "1:3", if I have data range like "A1:A3" then how should I proceed?

[Bob Phillips]

1:3 doesn't refer to cells here it is the 1,2,3 in

(1-2)^2 + (2-2)^2 + (3-2)^2

The 2 in that would be the A1 in the fromula.

[arrun]

I understood, however I want numbers stored in, suppose, B1, B2, B3 not a particular "1", "2', "3"

[arrun]

For example I tried with :
=SUMSQ(B1:B3- AVERAGE(B1:B3))

However I am not getting desired result