2009-12-09

Computing the standard deviation of timings in OpenOffice

I took some timings during a user study, and tried to use NeoOffice (aka OpenOffice for OS X) to evaluate the results. I needed to compute the standard deviation of the timings. The data itself was in a format MM:SS. What does work is computing the sum, like this:

=SUMME(A1:A20)

Note that I am using the german function names. I don't know all the english function names, so you have to check that yourself. Now I tried to compute the standard deviation as follows:

=STABW(A1:A20)

But all I got was 00:00 as the answer. I couldn't get an answer why that is the case, not even by the helpful people in #openoffice.org on Freenode. What I did find out is that you can do the computation when converting the timings to another format, namely just seconds. You can do this in the following way:

=MINUTE(A1)*60+SEKUNDE(A1)

So you basically need another column, filled with the above formula, say column B, and then you can compute the standard deviation: