Negative Standard Deviations

I am not sure that this is an appropriate place to ask a question about Excel's stats functions as opposed to StatistiXL. My reasoning is just that many here must use Excel often for statistical purposes and perhaps would know - or would like to know - the answer.

Several times I have seen Excel's STDEV function return a negative value as the standard deviation of a some collection of numbers. Perhaps I am ignorant but this seems quite wrong to me. Does anyone know why this happens, what it means, and what, if anything can be done about it?

Thanks for any help.



  • Hi Lance

    I know some of Excel's stats routines are a bit buggy which is why we use our own code with statistiXL rather taking a legup from the built in stuff. I wasn't aware of an issue with STDEV though. Do you have an example list that shows this behaviour?


  • I have been experimenting with bootstrapping using Excel's data table feature (not original to me). In bootstrapping it is common to estimate the standard error of the bootstrapped means (if you are bootstrapping means) by taking the standard deviation over the bootstrapped means. Anyway these estimates have quite frequently turned out to be negative!

    I can't work out how to attach a file to this message . If I could do that I could show you several examples!

    I have been hunting on the web and it seems that others have had a similar experience. See

    What is nice about the above is that he gives some VBA code to use in place of Excel's STDEV function. Still it is a nuisance.

    Thanks for the reply - much appreciated.


  • OK - I think I've got to eat some humble pie.

    I found a small error that I had inadvertantly copied across several spreadsheets (there was a "minus sign" in front of the STDEV function).

    Even with 2000 resamples I have not got any negative standard deviations now that I have eliminated that mistake. In addition I couldn't replicate the results given in the Pottel file mentioned above. So it seems that Microsoft did fix the STDEV function in the 2003 release.

    I am sorry I have created all this fuss.

    By the way, the Bootstrap procedure using Data Tables seems to work quite well even for large samples. I have, for example, a file of 691 rape cases that were reported to a rape crisis centre. I wanted to know about the extent of differences between sub-groups in that sample (e.g., rapists who chose to use a condom versus rapists who did not) and how reliable such proportions are. Anyway, so far, Bootstrapping seems to give reasonable answers. I am still experimenting, though.

  • Good news Lance, I'm glad things seem to be working now.


Sign In or Register to comment.