Ask LibreOffice  RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenTue, 12 May 2020 10:06:38 +0200Standard deviation spreadsheet  unexpected resulthttps://ask.libreoffice.org/en/question/243700/standarddeviationspreadsheetunexpectedresult/ I have set up a spreadsheet to demonstrate to students how to calculate standard deviation. The result that I have calculated matches the result given with the data, from the source. However, it differs from the result of the spreadsheet formula...<br />
=STDEV(B2:B20)
Is anyone able to explain why the function STDEV might not give the result expected?
I shall be happy to share my spreadsheet if attachments are possible on this forum?
The data set I am working with is available here:<br />
https://www.rgs.org/CMSPages/GetFile.aspx?nodeguid=2bc6c27a7d33402fb327e6bec35af7be
My step by step spreadsheet calculations involve...<br />
(1) Calculate the mean.<br />
(2) Subtract the mean from each value in the set.<br />
(3) Calculate the square of each value in (2).<br />
(4) Sum the vales from (3).<br />
(5) Divide (4) buy n.<br />
(6) Take the square root of (5).
Mon, 11 May 2020 14:46:27 +0200https://ask.libreoffice.org/en/question/243700/standarddeviationspreadsheetunexpectedresult/Comment by Opaque for <p>I have set up a spreadsheet to demonstrate to students how to calculate standard deviation. The result that I have calculated matches the result given with the data, from the source. However, it differs from the result of the spreadsheet formula...<br>
=STDEV(B2:B20)</p>
<p>Is anyone able to explain why the function STDEV might not give the result expected?</p>
<p>I shall be happy to share my spreadsheet if attachments are possible on this forum?</p>
<p>The data set I am working with is available here:<br><a href="https://www.rgs.org/CMSPages/GetFile.aspx?nodeguid=2bc6c27a7d33402fb327e6bec35af7be">https://www.rgs.org/CMSPages/GetFile....</a></p>
<p>My step by step spreadsheet calculations involve...<br>
(1) Calculate the mean.<br>
(2) Subtract the mean from each value in the set.<br>
(3) Calculate the square of each value in (2).<br>
(4) Sum the vales from (3).<br>
(5) Divide (4) buy n.<br>
(6) Take the square root of (5).</p>
https://ask.libreoffice.org/en/question/243700/standarddeviationspreadsheetunexpectedresult/?comment=243713#postid243713Oh sorry, I oversaw the statement
@Meneer: my apologies for the comment above.
@Mike Kaganski  Thanks to point me to that.Mon, 11 May 2020 15:39:25 +0200https://ask.libreoffice.org/en/question/243700/standarddeviationspreadsheetunexpectedresult/?comment=243713#postid243713Comment by Mike Kaganski for <p>I have set up a spreadsheet to demonstrate to students how to calculate standard deviation. The result that I have calculated matches the result given with the data, from the source. However, it differs from the result of the spreadsheet formula...<br>
=STDEV(B2:B20)</p>
<p>Is anyone able to explain why the function STDEV might not give the result expected?</p>
<p>I shall be happy to share my spreadsheet if attachments are possible on this forum?</p>
<p>The data set I am working with is available here:<br><a href="https://www.rgs.org/CMSPages/GetFile.aspx?nodeguid=2bc6c27a7d33402fb327e6bec35af7be">https://www.rgs.org/CMSPages/GetFile....</a></p>
<p>My step by step spreadsheet calculations involve...<br>
(1) Calculate the mean.<br>
(2) Subtract the mean from each value in the set.<br>
(3) Calculate the square of each value in (2).<br>
(4) Sum the vales from (3).<br>
(5) Divide (4) buy n.<br>
(6) Take the square root of (5).</p>
https://ask.libreoffice.org/en/question/243700/standarddeviationspreadsheetunexpectedresult/?comment=243712#postid243712@Opaque: note that OP wrote:
> I shall be happy to share my spreadsheet if attachments are possible on this forum?
... which means that only technical problem prevented OP from uploading the sample document.Mon, 11 May 2020 15:36:14 +0200https://ask.libreoffice.org/en/question/243700/standarddeviationspreadsheetunexpectedresult/?comment=243712#postid243712Comment by Opaque for <p>I have set up a spreadsheet to demonstrate to students how to calculate standard deviation. The result that I have calculated matches the result given with the data, from the source. However, it differs from the result of the spreadsheet formula...<br>
=STDEV(B2:B20)</p>
<p>Is anyone able to explain why the function STDEV might not give the result expected?</p>
<p>I shall be happy to share my spreadsheet if attachments are possible on this forum?</p>
<p>The data set I am working with is available here:<br><a href="https://www.rgs.org/CMSPages/GetFile.aspx?nodeguid=2bc6c27a7d33402fb327e6bec35af7be">https://www.rgs.org/CMSPages/GetFile....</a></p>
<p>My step by step spreadsheet calculations involve...<br>
(1) Calculate the mean.<br>
(2) Subtract the mean from each value in the set.<br>
(3) Calculate the square of each value in (2).<br>
(4) Sum the vales from (3).<br>
(5) Divide (4) buy n.<br>
(6) Take the square root of (5).</p>
https://ask.libreoffice.org/en/question/243700/standarddeviationspreadsheetunexpectedresult/?comment=243704#postid243704Do you expect potential contributors to figure out on their own, which data you have used from the link to a PDF, recreate formulas based on your description and compare with `STDEV()` result, while it could be so simple, if you'd upload your Calc sheet with the *incorrect* calculation (but may be you are looking for `STDEVP()`)Mon, 11 May 2020 14:56:37 +0200https://ask.libreoffice.org/en/question/243700/standarddeviationspreadsheetunexpectedresult/?comment=243704#postid243704Answer by Opaque for <p>I have set up a spreadsheet to demonstrate to students how to calculate standard deviation. The result that I have calculated matches the result given with the data, from the source. However, it differs from the result of the spreadsheet formula...<br>
=STDEV(B2:B20)</p>
<p>Is anyone able to explain why the function STDEV might not give the result expected?</p>
<p>I shall be happy to share my spreadsheet if attachments are possible on this forum?</p>
<p>The data set I am working with is available here:<br><a href="https://www.rgs.org/CMSPages/GetFile.aspx?nodeguid=2bc6c27a7d33402fb327e6bec35af7be">https://www.rgs.org/CMSPages/GetFile....</a></p>
<p>My step by step spreadsheet calculations involve...<br>
(1) Calculate the mean.<br>
(2) Subtract the mean from each value in the set.<br>
(3) Calculate the square of each value in (2).<br>
(4) Sum the vales from (3).<br>
(5) Divide (4) buy n.<br>
(6) Take the square root of (5).</p>
https://ask.libreoffice.org/en/question/243700/standarddeviationspreadsheetunexpectedresult/?answer=243707#postid243707Hello,
probably [OASIS Standard documentation  6.18.72 STDEV](http://docs.oasisopen.org/office/v1.2/os/OpenDocumentv1.2ospart2.html#STDEV) clarifies your mismatch (`STDEV()` is based on `1/(n1)`, while your procedure divides by `n` in step (5))
Your procedure is how function `STDEVP()` works (see also [OASIS Standard documentation  6.18.74 STDEVP]( http://docs.oasisopen.org/office/v1.2/os/OpenDocumentv1.2ospart2.html#__RefHeading__1018770_715980110)).
Hope that helps.
*If the answer helped to solve your problem, please click the check mark (✔) next to the answer.*
Mon, 11 May 2020 15:15:15 +0200https://ask.libreoffice.org/en/question/243700/standarddeviationspreadsheetunexpectedresult/?answer=243707#postid243707Comment by keme for <p>Hello,</p>
<p>probably <a href="http://docs.oasisopen.org/office/v1.2/os/OpenDocumentv1.2ospart2.html#STDEV">OASIS Standard documentation  6.18.72 STDEV</a> clarifies your mismatch (<code>STDEV()</code> is based on <code>1/(n1)</code>, while your procedure divides by <code>n</code> in step (5))</p>
<p>Your procedure is how function <code>STDEVP()</code> works (see also <a href="http://docs.oasisopen.org/office/v1.2/os/OpenDocumentv1.2ospart2.html#__RefHeading__1018770_715980110">OASIS Standard documentation  6.18.74 STDEVP</a>).</p>
<p>Hope that helps.</p>
<p><em>If the answer helped to solve your problem, please click the check mark (✔) next to the answer.</em></p>
https://ask.libreoffice.org/en/question/243700/standarddeviationspreadsheetunexpectedresult/?comment=243825#postid243825@Meneer, you answered
> ... I am aware of the difference between population and sample standard deviation. ...
Please accept my apologies!
Of course you would be aware of that, given that you are teaching a subject where this is an element. I must read my audience better, it seems. No offense was intended by my previous comment. My bad!
*I still leave that elaboration as it stands, seeing that it **just might** be useful to other users searching for similar subjects.*Tue, 12 May 2020 10:06:38 +0200https://ask.libreoffice.org/en/question/243700/standarddeviationspreadsheetunexpectedresult/?comment=243825#postid243825Comment by keme for <p>Hello,</p>
<p>probably <a href="http://docs.oasisopen.org/office/v1.2/os/OpenDocumentv1.2ospart2.html#STDEV">OASIS Standard documentation  6.18.72 STDEV</a> clarifies your mismatch (<code>STDEV()</code> is based on <code>1/(n1)</code>, while your procedure divides by <code>n</code> in step (5))</p>
<p>Your procedure is how function <code>STDEVP()</code> works (see also <a href="http://docs.oasisopen.org/office/v1.2/os/OpenDocumentv1.2ospart2.html#__RefHeading__1018770_715980110">OASIS Standard documentation  6.18.74 STDEVP</a>).</p>
<p>Hope that helps.</p>
<p><em>If the answer helped to solve your problem, please click the check mark (✔) next to the answer.</em></p>
https://ask.libreoffice.org/en/question/243700/standarddeviationspreadsheetunexpectedresult/?comment=243718#postid243718Good answer! Some background may still be in order.
There is a reason why the `STDEV()` "**sample** standard deviation" differs from the `STDEVP()` "**population** standard deviation".
"How much variation is there?"

`STDEVP()` is meant to be used if you have a dataset which encompasses the entire **population** in question. It is a *determined* value.
"What uncertainty is there to my statistical analysis"

STDEV() is meant to be used for a **sample** set. The (n1) divisor gives a larger result, which signifies an added insecurity, represented numerically by what you may think of as an "inflated deviation". It is an *approximation* which depends on the selection of the sample set. The "inflating" diminishes if you take a larger set of samples.Mon, 11 May 2020 15:55:06 +0200https://ask.libreoffice.org/en/question/243700/standarddeviationspreadsheetunexpectedresult/?comment=243718#postid243718Answer by Meneer for <p>I have set up a spreadsheet to demonstrate to students how to calculate standard deviation. The result that I have calculated matches the result given with the data, from the source. However, it differs from the result of the spreadsheet formula...<br>
=STDEV(B2:B20)</p>
<p>Is anyone able to explain why the function STDEV might not give the result expected?</p>
<p>I shall be happy to share my spreadsheet if attachments are possible on this forum?</p>
<p>The data set I am working with is available here:<br><a href="https://www.rgs.org/CMSPages/GetFile.aspx?nodeguid=2bc6c27a7d33402fb327e6bec35af7be">https://www.rgs.org/CMSPages/GetFile....</a></p>
<p>My step by step spreadsheet calculations involve...<br>
(1) Calculate the mean.<br>
(2) Subtract the mean from each value in the set.<br>
(3) Calculate the square of each value in (2).<br>
(4) Sum the vales from (3).<br>
(5) Divide (4) buy n.<br>
(6) Take the square root of (5).</p>
https://ask.libreoffice.org/en/question/243700/standarddeviationspreadsheetunexpectedresult/?answer=243733#postid243733Thanks for a simple, clear and very swift answer. I am aware of the difference between population and sample standard deviation. I had looked for, but not found the alternative function. My bad.<br />
=STDEVP()<br />
Indeed, gives the result I was expecting.Mon, 11 May 2020 17:36:50 +0200https://ask.libreoffice.org/en/question/243700/standarddeviationspreadsheetunexpectedresult/?answer=243733#postid243733Comment by m.a.riosv for <p>Thanks for a simple, clear and very swift answer. I am aware of the difference between population and sample standard deviation. I had looked for, but not found the alternative function. My bad.<br>
=STDEVP()<br>
Indeed, gives the result I was expecting.</p>
https://ask.libreoffice.org/en/question/243700/standarddeviationspreadsheetunexpectedresult/?comment=243771#postid243771This is not an answer, please use the comments.Mon, 11 May 2020 21:54:23 +0200https://ask.libreoffice.org/en/question/243700/standarddeviationspreadsheetunexpectedresult/?comment=243771#postid243771