View original (French)

Standarddeviation.si.ens

Solved
Princedes -  
ALS35 Posted messages 1034 Registration date   Status Member Last intervention   -
Hello,

I usually use the following formula to calculate the average over a duration between today - X years and today, in a table where the data changes dynamically:

=AVERAGEIFS(M:M,$L:$L,"<="&TODAY(),$L:$L,">="&DATE(YEAR(TODAY())-$Y$9,MONTH(TODAY()),DAY(TODAY())))

I would like to do the same thing with a standard deviation instead of an average, but the formula does not exist, and I don't know how to code in VBA.

Can you help me create a similar formula for standard deviation?

1 answer

  1.
    ALS35 Posted messages 1034 Registration date   Status Member Last intervention   147
     
    Hello,

    To test 
    =IFERROR(STDEV.S(IF(($L:$L<=$C$1)*($L:$L>=$D$1)*$M:$M=0,"",($L:$L<=$C$1)*($L:$L>=$D$1)*$M:$M)),"")


    Array formula to be validated by Ctrl+Shift+Enter.
    The formula is surrounded by braces {} if okay

    and with, to lighten the formula
    in C1 or elsewhere: TODAY()
    in D1 or elsewhere your formula TODAY()- x years which you can also write like this: 
    =EDATE(TODAY(),-12*$Y$9)


    Beware of heavy formula, instead of taking all the columns L and M, limit the ranges to the strict necessary L1:L1000 for example

    Best regards
    0
    1. Princedes
       
      Hello,

      Thank you for your response. I took some time to study it to try to understand what is not working. However, I can't find anything. It's just that the formula does not return any result: the cell is empty.
      0
    2. ALS35 Posted messages 1034 Registration date   Status Member Last intervention   147 > Princedes
       
      Hello,

      Well, send your table or an example and I will take a look.

      Best regards
      0
    3. Princedes > ALS35 Posted messages 1034 Registration date   Status Member Last intervention  
       
      Hello, I found the mistake! It's my fault, in my database (which is rich with about 5000 lines), downloaded from an external source, there are #N/A N/A (for the days when the calculation from the external source did not occur).

      I have tested replacing the #N/A N/A with 0 and it works correctly. However, my database evolves every day and re-downloads from the external source, so manually processing the #N/A N/A will not work or will be very tedious.

      How can I modify the formula to tell it to ignore the #N/A N/A?

      Thank you in advance for your valuable help.

      Best regards,
      0
    4. ALS35 Posted messages 1034 Registration date   Status Member Last intervention   147 > Princedes
       
      Hello,

      If you have Excel 2013 or later, try this slightly simplified formula with a test for #N/A (IFERROR) 
      =IFERROR(STDEVP(IF(($L$1:$L$20<=$C$1)*($L$1:$L$20>=$D$1)*(IFNA($M$1:$M$20,0))=0,"",$M$1:$M$20)),"")

      Adjust the ranges L1:L20 and M1:M20 to your file
      Note that the actual unavailable value in Excel is #N/A and not just N/A which is text, so you might need to adjust this according to your file

      Best regards
      0
    5. Princedes > ALS35 Posted messages 1034 Registration date   Status Member Last intervention  
       
      Does your formula work if the value returned by the cell is exactly "#N/A N/A"? Because the formula in the cell is giving me this error message.
      0