View original (French)

Standarddeviation.si.ens

Solved
Anonymous user -  
ALS35 Posted messages 1034 Registration date   Status Membre 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 réponse

Réponse 1 / 1
ALS35 Posted messages 1034 Registration date   Status Membre 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
Anonymous user
 
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
ALS35 Posted messages 1034 Registration date   Status Membre Last intervention   147 > Anonymous user
 
Hello,

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

Best regards
0
Anonymous user > ALS35 Posted messages 1034 Registration date   Status Membre 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
ALS35 Posted messages 1034 Registration date   Status Membre Last intervention   147 > Anonymous user
 
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
Anonymous user > ALS35 Posted messages 1034 Registration date   Status Membre 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