How to calculate SD or SE for binary data set?

rajamdade

New Member
Joined
Jun 14, 2014
Messages
35
Office Version
  1. 2016
Platform
  1. Windows
I am having binary datasets of 0 and 1, the data consist of results achieved from classification tasks as '1' for classified and '0' for not classified. So, how to calculate SD or SE for such datasets?

I have got the formula for SD:
The standard deviation of the 1s and 0s is the square root fo the mean of the squared deviations of the 1s and 0s from the mean of the 1s and 0s. Thus, where x is 1 or 0, and M is the mean x,
the standard deviation of X= SQRT((SUM((x-M)^2))/N

could you please suggest the excel formula to calculate the SD as above (I have tried it as attached, but there is an error), else suggest more solution for SD or SE on binary datasets.
Thanks!


SD excel screenshot.jpg
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
why not use one of the excel standard deviation functions, I would suggest :
=STDEV.P(B2:B415)
 
Upvote 0
I would encourage you to review more information about the binomial distribution...e.g., Binomial distribution - Wikipedia
Given the type of data you've described, that is most likely the distribution that will be relevant. The approach above assumes a normal distribution, and while there are normal approximations for binomial distributions, there are typically certain rules of thumb that assess whether such an approximation is reasonable. As shown in the reference, the mean, or expected value, is n * p, where p is the probability of some observable outcome; and the variance is given by Var = n * p * (1-p), where n is the number of trials, p the probability of one outcome, and 1-p (sometimes shown as "q") is the probability of the other outcome. So you would use your observed outcomes or other information to estimate p, and you know n.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,213
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top