Tryintouseexcel
New Member
 Joined
 Aug 7, 2020
 Messages
 11
 Office Version

 365
 Platform

 Windows
I need to take the standard deviation of a group of numbers. This group of numbers includes cells that have the #div/0!. How can I make the standard deviation function ignore these cells?
I have tried this: =STDEV(IF(ISNUMBER(U46:U410),U46:U410))/SQRT(COUNT(U46:U410)), and it returns a value, but it is an incorrect value. I tested this by removing the cells with the #div/0! and running the standard deviation function on them.
How do I make the standard deviation function ignore anything that is not a numerical value?
I have pasted a portion of my worksheet below. The fist column is where I need the standard deviation of the data displayed. The next column is the data including cells with the #div/0! error displayed. The next column after that is the same data with the #div/0! cells removed. The last column, with just one cell displaying a value is the test I ran where i did the STDEV function on the data with the #div/0! cells removed.
Thanks
I have tried this: =STDEV(IF(ISNUMBER(U46:U410),U46:U410))/SQRT(COUNT(U46:U410)), and it returns a value, but it is an incorrect value. I tested this by removing the cells with the #div/0! and running the standard deviation function on them.
How do I make the standard deviation function ignore anything that is not a numerical value?
I have pasted a portion of my worksheet below. The fist column is where I need the standard deviation of the data displayed. The next column is the data including cells with the #div/0! error displayed. The next column after that is the same data with the #div/0! cells removed. The last column, with just one cell displaying a value is the test I ran where i did the STDEV function on the data with the #div/0! cells removed.
Thanks
STDV 2021 Data  
1.743438118  9.52381  9.52381  11.16345  
1.626016  1.626016  
15.82734  15.82734  
31.93277  31.93277  
6.593407  6.593407  
#DIV/0!  1.769912  
1.769912  11.76471  
11.76471  6.451613  
6.451613  7.407407  
7.407407  7.692308  
7.692308  2.020202  
2.020202  2.666667  
#DIV/0!  11.11111  
2.666667  13.04348  
11.11111  5.128205  
13.04348  1.869159  
5.128205  10  
1.869159  0  
10  0  
0  55.88235  
0  5.555556  
55.88235  0  
5.555556  20.22472  
0  2.531646  
20.22472  0  
2.531646  2.531646  
0  4.651163  
2.531646  3.333333  
4.651163  2.469136  
3.333333  13.33333  
2.469136  0  
13.33333  0  
0  7.407407  
0  0  
7.407407  4.724409  
0  2.020202  
4.724409  6.896552  
2.020202  12.98701  
6.896552  36.8932  
12.98701  19.78022  
36.8932  3.571429  
19.78022  
3.571429  
#DIV/0!  
#DIV/0!  
#DIV/0!  
#DIV/0!  
#DIV/0!  
#DIV/0!  
#DIV/0!  
#DIV/0!  
#DIV/0!  
#DIV/0!  
#DIV/0!  
#DIV/0!  
#DIV/0!  
#DIV/0!  
#DIV/0!  
#DIV/0! 