+ Reply to Thread
Results 1 to 22 of 22

help to calculate standard deviation

  1. #1
    Registered User
    Join Date
    03-12-2015
    Location
    france
    MS-Off Ver
    7
    Posts
    31

    help to calculate standard deviation

    hello

    I want to calclculate the standard deviation for each number index for the same period (2006 to 2014). for exemple what is the standard deviation for the index number 10009 for this period. I have the same period for all the index number and the index numbers are different.

    I've attached my file it's just an example of my database where I have almost 600 index number and that's why I want a code to run for that.

    Thank you
    Attached Files Attached Files

  2. #2
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: help to calculate standard deviation

    See if this works for you. Place this formula in cell D10
    Please Login or Register  to view this content.
    1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG
    You don't have to add Rep if I have helped you out (but it would be nice), but please mark the thread as SOLVED if your issue is resolved.

    Tom

  3. #3
    Forum Contributor HerryMarkowitz's Avatar
    Join Date
    09-10-2012
    Location
    Istanbul / Turkey
    MS-Off Ver
    2013 - Win10 - 64bit
    Posts
    1,007

    Re: help to calculate standard deviation

    First of all we need understand your question.
    So, please check enclosed file, D10 cell.
    Then tell us if I am on the right way...
    Sub DontForgetThese()
         If Your thread includes any code Then Please use code tags...
         If Your thread has been solved Then Please mark as solved...
         If Anybody has helped to you Then Please add reputation...
    End Sub

  4. #4
    Registered User
    Join Date
    03-12-2015
    Location
    france
    MS-Off Ver
    7
    Posts
    31

    Re: help to calculate standard deviation

    no it doesn't work I have an error message

  5. #5
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: help to calculate standard deviation

    Did you place the code in the sample workbook you provided us?

  6. #6
    Registered User
    Join Date
    03-12-2015
    Location
    france
    MS-Off Ver
    7
    Posts
    31

    Re: help to calculate standard deviation

    yes that's what I want (ecart type) but the problem is that I have more than 500 index numbers and I have to calculate the standanrd deviation for each one (so I will have 500 standard deviations to calculate). I tried with "DO ..LOOP" but it didn't work so I ask for help
    thank you
    (i don't know if that will help but I have a panel data to treat)

  7. #7
    Registered User
    Join Date
    03-12-2015
    Location
    france
    MS-Off Ver
    7
    Posts
    31

    Re: help to calculate standard deviation

    yes it's the standard deviation of the "ratio" for each "index number"

  8. #8
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: help to calculate standard deviation

    The formula I gave worked on the sample you attached in here. I don't however, know how to do it for all of them without copy and paste.
    Last edited by gmr4evr1; 03-12-2015 at 06:49 PM.

  9. #9
    Registered User
    Join Date
    03-12-2015
    Location
    france
    MS-Off Ver
    7
    Posts
    31

    Re: help to calculate standard deviation

    no I always have the same error message

  10. #10
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: help to calculate standard deviation

    I've attached the one I have. Herry and I came up with the "same" answers, mine is not in percentage format.
    Attachment 382813

  11. #11
    Registered User
    Join Date
    03-12-2015
    Location
    france
    MS-Off Ver
    7
    Posts
    31

    Re: help to calculate standard deviation

    ok thank you. so there is no way to do this automaticly for the 600 index numbers that I have

  12. #12
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: help to calculate standard deviation

    You're welcome. There probably is a way, I'm just not knowledgeable enough to be able to help with that part of it.

  13. #13
    Registered User
    Join Date
    03-12-2015
    Location
    france
    MS-Off Ver
    7
    Posts
    31

    Re: help to calculate standard deviation

    I find this code but I don't know how to manage it for my example

    Function StdDev(k As Long, Arr() As Single)
    Dim i As Integer
    Dim avg As Single, SumSq As Single

    avg = Mean(k, Arr)
    For i = 1 To k
    SumSq = SumSq + (Arr(i) - avg) ^ 2
    Next i

    StdDev = Sqr(SumSq / (k - 1))

    End Function

  14. #14
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: help to calculate standard deviation

    If your data is in that consistent format, i.e. 9 rows for each number then you can simply "fill down"

    Just enter the suggested formula in D10, select the range D2:D10 and fill down by double-clicking the "fill-handle" on the bottom right of D10
    Audere est facere

  15. #15
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: help to calculate standard deviation

    Quote Originally Posted by lana86 View Post
    I want to calclculate the standard deviation for each number index for the same period (2006 to 2014). for exemple what is the standard deviation for the index number 10009 for this period. I have the same period for all the index number and the index numbers are different.

    I've attached my file it's just an example of my database where I have almost 600 index number and that's why I want a code to run for that.
    Array-enter the following formula into D2 (press ctrl+shift+Enter instead of Enter):

    =IFERROR(IF(B2<>B3,STDEVP(IF($B$2:B2=B2,$C$2:C2)),""),"")

    Caveat: You might use semicolon (";") instead of comma (",") as a parameter separator.

    Change STDEVP to STDEV if you want the sample standard deviation.

    Then copy D2 and paste into D3:D600.

  16. #16
    Registered User
    Join Date
    03-12-2015
    Location
    france
    MS-Off Ver
    7
    Posts
    31

    Re: help to calculate standard deviation

    but when I fill down the sdv of the index numbers will be mixed and I will have sdv for each date. ok maybe you gave me an idea: I will fill down and then with "do loop" I will "copy" only the rows corresponding to 2014.

  17. #17
    Registered User
    Join Date
    03-12-2015
    Location
    france
    MS-Off Ver
    7
    Posts
    31

    Re: help to calculate standard deviation

    joeu2004 : I've tried your formula but it's the same "error message"
    =IFERROR(IF(B2<>B3;STDEVP(IF($B$2:B2=B2,$C$2:C2)),""),"")

  18. #18
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: help to calculate standard deviation

    Quote Originally Posted by lana86 View Post
    but when I fill down the sdv of the index numbers will be mixed and I will have sdv for each date.
    No. My suggestion is that you leave D2:D9 blank and then put the formula in D10

    Then when you select D2:D10 and fill down it creates a similar pattern all the way down, 8 blanks followed by one cell with the formula. That should work as long as you consistently have 9 cells for each number.....otherwise you might want to follow joeu2004's suggestion.

  19. #19
    Registered User
    Join Date
    03-12-2015
    Location
    france
    MS-Off Ver
    7
    Posts
    31

    Re: help to calculate standard deviation

    daddylonglegs: thank you thank you thank you....................................... so much
    you have solved my problem I was going to calculate it for each of the 600

  20. #20
    Registered User
    Join Date
    03-12-2015
    Location
    france
    MS-Off Ver
    7
    Posts
    31

    Re: help to calculate standard deviation

    thank you for everybody for your help

  21. #21
    Registered User
    Join Date
    03-12-2015
    Location
    france
    MS-Off Ver
    7
    Posts
    31

    Re: help to calculate standard deviation

    I have an other question now. with the same attached file, I want to get for 2014 the percentage of compagnies (represented by "index number") which have a ratio > 1 and those with a ratio < 1 and then I want to represent these percentages with circle chart. (I hope that my question is clear )

  22. #22
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: help to calculate standard deviation

    Quote Originally Posted by lana86 View Post
    joeu2004 : I've tried your formula but it's the same "error message"
    =IFERROR(IF(B2<>B3;STDEVP(IF($B$2:B2=B2,$C$2:C2)),""),"")
    I don't read minds, so I don't know what "same error message" you are getting.

    I notice that you heeded (paid attention to) my caveat and replaced one comma separator with a semicolon.

    But if that is correct for your region, you need to replace all commas with semicolons, to wit:

    =IFERROR(IF(B2<>B3;STDEVP(IF($B$2:B2=B2;$C$2:C2));"");"")

    And of course, you might need to replace the English words IFERROR, IF and STDEVP with French equivalents.

    The point of the formula is to make it easy to copy down the column. Even though there would be a formula next to every index, the formula is designed to calculate STDEVP only in the last row of a similar indexes.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] VBA to calculate Standard Deviation
    By schmidtkicker in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-11-2014, 06:17 AM
  2. Calculate Standard Deviation
    By goss in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-05-2013, 05:11 AM
  3. Need a more efficient way to calculate Standard Deviation
    By StevenAlberta in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-24-2012, 03:17 PM
  4. Calculate one standard deviation from mean
    By missyreiber in forum Excel General
    Replies: 3
    Last Post: 09-08-2010, 11:40 AM
  5. [SOLVED] How to calculate 2 standard deviation?
    By Li in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-12-2005, 05:06 PM

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