mean and standard deviation of triangular matrix

What are the upper corner values in the array? Zero? Blanks?

If they're blanks, no problem, they're ignored by the functions, carry on.

If they're zeros... finding the mean is trivial (decompose to sum / COUNTIF), but the STDEV has to actually be built up because we want to ignore values of 0 going into n, the number of elements.

Well, I'm going to split up the code, to make it easier to follow what's going on, but you could jam it all together into just two cells if you wanted.

num = COUNTIF(range, ">0") // a user-defined variable for n, number of elements

MEAN = SUM(range) / num // you could do SUMIF(range,">0"), but adding zeros to a summation... is meaningless.

inner = SUMPRODUCT(range,range) / num // a user-defined variable for the inner term of the STDEV math

STDEV = SQRT(inner - inner^2)

Thank you, both of you.

In fact, I resolved a problem using R.

MrShorty, I did as you explained to chcek that my results would be the same, it's ok.

ben, thank you but it would not work because I got a couple of "0" distances so they would have been neglected.

Thank you once again, I learnt something!

have a nice day


lucinka