topic Re: Function of STDEVP in Excel in Discussions

Function of STDEVP in Excel

Dennisbur — Fri, 09 Jun 2023 00:58:04 GMT

Hello
I tried to use the same function as STDEVP in Excel and I didn't find an exact analog in JMP where I could receive the same result.
For example, if I will take four numeric numbers as well as 1,6,8,9, the STDEV should be 3.08

but if I'm using formula SDT DEV in JMP I'm receiving result of 3.56

I will appreciate it if you could direct me to the right place in the formula where I can receive the same result of 3.08?

Thank you

Re: Function of STDEVP in Excel

Dan_Obermiller — Mon, 05 Dec 2022 13:30:51 GMT

The formula STDEVP is the standard deviation of the population and uses a divisor of n in the formula for the variance. It is intended to be used only when you have all of the data values from the population. This is rarely the case. Instead, we have a sample most of the time. The variance for a sample uses a divisor of n-1 as that provides an unbiased estimate of the population variance. JMP uses this formula since it is what is needed in the vast majority of cases.

Knowing this allows you to see the conversion to get the STDEVP result. If your data is in "Column 1", this formula will give you what you need:

Root( Col Std Dev( :Column 1 ) ^ 2 * ((N Row() - 1) / N Row()), 2 )

Note that the formula needs a variance which is why the standard deviation is squared, multiplied by n-1/n to "change" the denominator and then the square root to return it to a standard deviation.

Re: Function of STDEVP in Excel

Victor_G — Mon, 05 Dec 2022 13:53:26 GMT

Hello @Dennisbur,

You have two different values because the formula behind are not exactly the same :

When calculating the standard deviation for a population, you divide the sum of square of the differences between mean and value by the total number of values N, in your case 4, before taking the root square. You then find 3,08 (see formula attached).
When calculating the standard deviation for a sample (a part of the population), the denominator changes and you have N-1 instead of N (see formula StdDev_Sample attached), as you use 1 degree of freedom from a fixed sample to produce an estimate of the sample mean. It is known as Bessel's correction. You then have the value 3,56 proposed by JMP.

You can have a look at the comparisons and calculations in the JMP file provided and statistical details here : Explanation of Summary Statistics (jmp.com)

Hope it will help you,

Re: Function of STDEVP in Excel

Dennisbur — Tue, 06 Dec 2022 08:33:42 GMT

Thank you, Victor

For your quick response

So if I understood well, JMP doesn't have the function STDEVP like in Excel, and to calculate the function, I should use long manual formula or combine it with

STDEV + Average + Square Difference?

Re: Function of STDEVP in Excel

Victor_G — Tue, 06 Dec 2022 09:26:51 GMT

Hi @Dennisbur,

The default calculation in JMP for Std Dev is the one with Bessel's correction. As emphasized by @Dan_Obermiller, this is the most frequent case as you're frequently in the situation where you have data from a sample (hopefully a representative one), but not from the entire population.
As you can see in the datatable I provided, it is quite simple to re-create the formula if you need it.
On a side note, I would highly recommend to format data otherwise : observations in rows, and parameters/factors in columns.
This way, your formula will be also easier to create and maintain.

Here is the formula you can use with a column named "Values" containing your data. You will only need the individual values for your observations in this column (the number of rows is automatically calculated with the function "N Row()") :

Root( Col Sum( (:Values - Col Mean( :Values )) ^ 2 ) / N Row() )

If your column is named differently, just change the ":Values" with ":Name" (Name being the name of your column).
Hope this will help you,

Re: Function of STDEVP in Excel

Dennisbur — Tue, 27 Dec 2022 13:34:16 GMT

Hello

I have an additional question for Std Dev

If I have two or more names in the table

And I need calculate STD DEV per name (Column1). How can I group this formula by Column 1?

Because if I'm using Col Sum, the formula runs to all column and don't separate the data by name (Column 1)

Re: Function of STDEVP in Excel

jthi — Tue, 27 Dec 2022 14:19:58 GMT

Col Sum does support <byVar> which you can use in this case (as do other similar functions). You will most likely also have to update Col Mean() and change N Row() to use other function (Col Number() could be one option).