Make Standard Deviation Function Ignore Cells with #DIV/0!

T

Tryintouseexcel

New Member
Joined
Aug 7, 2020
Messages
11
Office Version
  1. 365
Platform
  1. 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

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!​
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Sort by date Sort by votes
Tryintouseexcel said:
=STDEV(IF(ISNUMBER(U46:U410),U46:U410))/SQRT(COUNT(U46:U410)), and it returns a value, but it is an incorrect value
Click to expand...

The formula is correct. I suspect you did not array-enter it: press ctrl+shift+Enter instead of just Enter.
 
Upvote 0
joeu2004 said:
The formula is correct. I suspect you did not array-enter it: press ctrl+shift+Enter instead of just Enter.
Click to expand...
Thanks for responding. I did press ctrl+shift+enter, and the brackets did appear. The only reason I believe it is wrong is because I ran the test by running another standard deviation function on the same data set with the #div/0! cells removed and it returned a different value...
 
Upvote 0
Tryintouseexcel said:
I did press ctrl+shift+enter, and the brackets did appear. The only reason I believe it is wrong is because I ran the test by running another standard deviation function on the same data set with the #div/0! cells removed and it returned a different value...
Click to expand...

I don't understand. Are you saying that there is no longer a problem? Or are you saying that array-entered formula is returning a different result than some other formula?

Please use the XL2BB tool to present your data. That way, it is easy to see your formulas and to copy the data and formulas into an Excel worksheet.

Alternatively, upload an example Excel file (redacted) that demonstrates the problems to a file-sharing website, and post the download URL in a response here. I like box.net/files; others like dropbox.com. In any case, test the download URL first, being careful to log out of all websites that share the same login, in order to ensure that anyone else can access the file anonymously.

Some contributors object to that. But sometimes, it helps to see details that we cannot see with XL2BB.
 
Upvote 0
joeu2004 said:
I don't understand. Are you saying that there is no longer a problem? Or are you saying that array-entered formula is returning a different result than some other formula?

Please use the XL2BB tool to present your data. That way, it is easy to see your formulas and to copy the data and formulas into an Excel worksheet.

Alternatively, upload an example Excel file (redacted) that demonstrates the problems to a file-sharing website, and post the download URL in a response here. I like box.net/files; others like dropbox.com. In any case, test the download URL first, being careful to log out of all websites that share the same login, in order to ensure that anyone else can access the file anonymously.

Some contributors object to that. But sometimes, it helps to see details that we cannot see with XL2BB.
Click to expand...
Thanks, I will try using the XL2BB tool. I am getting off work soon. So probably will be Tuesday before I can do it. Thanks for helping.
 
Upvote 0
Another solution may be to enter the following array formula in C4

==STDEV.S(IF(ISNUMBER(A:A),A:A,"aaa"))


1613853107821.png



Kind regards

Saba
 
Upvote 0
PS.... IMHO, it would be better to avoid the #DIV/0 errors, in the first place. For example, =IFERROR(1/0,"").

Then you can use normally-entered STDEV(U46:U410) by pressing just Enter as usual.
 
Upvote 0
joeu2004 said:
I suspect you did not array-enter it: press ctrl+shift+Enter instead of just Enter.
Click to expand...

A really "dumb" speculation. If you had normally-entered a STDEV formula of that form, you would have gotten an error (#DIV/0), not an incorrect numeric result.

It is more likely that you have a typo, either in the array-entered STDEV formula itself, or in the data column (presumably U46:U410).

For the latter, you might have some additional values much lower in the column, where they escaped your gaze. (I had that very problem while testing a solution for another problem yesterday.)

The XL2BB capture below demonstrates the correctness of the array-entered formulas on the left (V45 and W45), compared to the extracted data (column X) and normally-entered formulas on the right (Y45 and Z45)

BTW, your original problem description is confusing. In particular, it is unclear whether 1.743438118 on the left is the result of array-entered STDEV(...)/COUNT(...), as you wrote, or STDEV(...).

It should be the latter for comparison purposes, because 11.16345 on the right is the result of normally-entered STDEV(...), not STDEV(...)/COUNT(...). This is demonstrated below.

In contrast, normally-entered STDEV(...)/COUNT(...) should result in 0.272279272182649, which is displayed as 0.272279 below -- assuming that your data is truly identical to the values that you posted.

(Your posted data might be rounded due to cell formats.)

For the XL2BB capture below, select or hover the cursor over cells to see formulas. Use the XL2BB scroll bar on the right.

stdev div0.xlsm
UVWXYZ
4511.163450.27227911.1634500.272279
469.5238109.523810
471.6260161.626016
4815.82734015.827340
4931.93277031.932770
506.5934076.593407
51#DIV/0!1.769912
521.76991211.764710
5311.7647106.451613
546.4516137.407407
557.4074077.692308
567.6923082.020202
572.0202022.666667
58#DIV/0!11.111110
592.66666713.043480
6011.1111105.128205
6113.0434801.869159
625.12820510.000000
631.8691590.000000
6410.0000000.000000
650.00000055.882350
660.0000005.555556
6755.8823500.000000
685.55555620.224720
690.0000002.531646
7020.2247200.000000
712.5316462.531646
720.0000004.651163
732.5316463.333333
744.6511632.469136
753.33333313.333330
762.4691360.000000
7713.3333300.000000
780.0000007.407407
790.0000000.000000
807.4074074.724409
810.0000002.020202
824.7244096.896552
832.02020212.987010
846.89655236.893200
8512.98701019.780220
8636.8932003.571429
8719.780220
883.571429
89#DIV/0!
90#DIV/0!
91#DIV/0!
92#DIV/0!
93#DIV/0!
94#DIV/0!
95#DIV/0!
96#DIV/0!
97#DIV/0!
98#DIV/0!
99#DIV/0!
100#DIV/0!
101#DIV/0!
102#DIV/0!
103#DIV/0!
104#DIV/0!
Sheet1
 
Upvote 0
Solution
What about using AGGREGATE which has an available parameter to exclude error values?

21 02 21.xlsm
UV
469.5238111.16345
471.626016
4815.82734
4931.93277
506.593407
51#DIV/0!
521.769912
5311.76471
546.451613
557.407407
567.692308
572.020202
58#DIV/0!
592.666667
6011.11111
6113.04348
625.128205
631.869159
6410
650
660
6755.88235
685.555556
690
7020.22472
712.531646
720
732.531646
744.651163
753.333333
762.469136
7713.33333
780
790
807.407407
810
824.724409
832.020202
846.896552
8512.98701
8636.8932
8719.78022
883.571429
89#DIV/0!
90#DIV/0!
91#DIV/0!
92#DIV/0!
93#DIV/0!
94#DIV/0!
95#DIV/0!
96#DIV/0!
97#DIV/0!
98#DIV/0!
99#DIV/0!
100#DIV/0!
101#DIV/0!
102#DIV/0!
103#DIV/0!
104#DIV/0!
SD
Cell Formulas
RangeFormula
V46V46=AGGREGATE(7,6,U46:U410)
U51,U89:U104,U58U51=1/0
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,214,553
Messages
6,120,179
Members
448,948
Latest member
spamiki

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top