mean and standard deviation of triangular matrix

Hello,

I got a lower triangular matrix of genetic distances between ma 85 samples and I just would like to get a mean and a standard deviation of these distances. Is it possible il excel, please ?

It's not just possible, it's downright easy.

mean = AVERAGE(top_left_cell:bottom_right_cell)
standard deviation = STDEV(top_left_cell:bottom_right_cell)

Dear ben,

thank you for replying, but I need to selec ONLY the lower triangle of my matrix whereas what you adviced me select the whole block (rectangle not triangle). Anyway, thank you for trying !

reagrds,


lucinka

So the real question is how to use a triangular range as an argument for these functions. Spreadsheets tend to work best on rectangular ranges.

The easiest (but I'm sure someone will say least elegant looking) might be something like this. The average and stdev functions can take up to 30 "arguments" in my version of Excel (not sure if this has increased in newer versions). So, if you can break up the triangle into 30 or fewer rectangles, it is fairly straightforward. Assuming a 10x10 matrix in the upper left corner of the spreadsheet:

=AVERAGE(A1:A10,B2:B10,C3:C10,D4:D10,...) (same for STDEV() function).

Tedious on first entry, but a straightforward function that shouldn't take any effort to understand or debug or change.

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