+ Reply to Thread
Results 1 to 3 of 3

Formula for three sigma rule of Standard Deviation

Hybrid View

  1. #1
    Registered User
    Join Date
    09-16-2009
    Location
    Singapore
    MS-Off Ver
    Excel 2007
    Posts
    9

    Formula for three sigma rule of Standard Deviation

    This an advance version of my earlier posting which was solved by the Great Palmetto. Hope he can help again.

    I want to find the correct formula to find answers using excel for the folliowing:

    1. >3Sds
    2. 3SDs<>2SDs
    3. 2SDs<>1SD
    4. <1SDs

    This question uses 3 sigma rule (see attached worksheet) and the formula needs to be re-arranged to meet new criteria.

    So, we need to consider both sides of nornmal distributiion (ND) curve. eg. when we say >3SD it means it should count all valuee falling in the rage "Mean(Average)+3SD AND Mean-3SD" i.e two extreme ends of the ND cureve. I have inserted a normal distribution diagram and colour coded the ranges so that it will be easy to identify. Please refer comments inserted in K7to N7, cells that contain formula that need to be reset.
    Attached Files Attached Files
    Last edited by King_Junior; 09-20-2009 at 11:21 PM.

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Formula for three sigma rule of Standard Deviation

    Select K24:R24, paste this in the formula bar,

    =TRANSPOSE(FREQUENCY(H3:H1164, K23:Q23))

    .. then press and hold tthe Ctrl and Shift keys, then press Enter.

           ---K--- ---L--- ---M--- --N--- --O--- --P--- --Q--- R
      22     -3      -2      -1      0      +1     +2     +3    
      23   -7.363% -4.875% -2.388% 0.100% 2.588% 5.076% 7.564%  
      24     10      18      110    422    468    112     15   7
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    09-16-2009
    Location
    Singapore
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Formula for three sigma rule of Standard Deviation

    Thank you very much

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1