ISNUMBER function...how do I use it?

Hlatigo

Well-known Member
Hello,

I know that if I wanted to include all nonzero numbers that I am suppose to use the ISNUMBER function but how do I implement it with other function such as STDEV(A1:A30).

That column has numbers I want to find what their standard deviation is but it also has zeros that I dont want to use in the calculation. Please...anyone has an answer?

Excel Facts

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
I do not believe ISNUMBER is the tool of choice. Try this Array formula:

=STDEV(IF(A1:A30>0,A1:A30,FALSE))

You must enter using CONTROL + SHIFT + ENTER

HTH
This message was edited by lenze on 2002-09-11 13:29

Array-enter:

=STDEV(IF(A1:A30,A1:A30))

To array-enter a formula, you need to hit control+shift+enter at the same time, not just enter.

thanks guys!!

I tried the >0 formula and it doesnt work because I get a divide by 0 return.

I did get a answer with the

=STDEV(IF(W10:W52,W10:W52))

but the answer is different then what I got on my own. What I did is I just went and chose the cells individually. But since I have about 200 to do, I dont think thats the best way since not all cells are populated exactly. Any ideas?

On 2002-09-11 13:41, Hlatigo wrote:
thanks guys!!

I tried the >0 formula and it doesnt work because I get a divide by 0 return.

I did get a answer with the

=STDEV(IF(W10:W52,W10:W52))

but the answer is different then what I got on my own. What I did is I just went and chose the cells individually. But since I have about 200 to do, I dont think thats the best way since not all cells are populated exactly. Any ideas?

You mean you doubt the result of the array formula (or the DSTDEV formula)?

Well to be honest,

I doubt the latter b/c I manually used -stdv() and selected the cells. The only thin I can assume is that the latter calculates standard deviation differently? (I say that with much hesitance)

On 2002-09-11 14:13, Hlatigo wrote:
Well to be honest,

I doubt the latter b/c I manually used -stdv() and selected the cells. The only thin I can assume is that the latter calculates standard deviation differently? (I say that with much hesitance)

The figure I posted shows 5 numbers. If you exlude 0's and use normally entered STDEV we get the same result as with the array or DSTDEV formula. Try:

=STDEV({1;2;3;4})

normally entered.

you are very much right. i am going to test my data again and see where I went wrong.

Replies
7
Views
536
Replies
2
Views
201
Replies
2
Views
1K
Replies
15
Views
574
Replies
2
Views
146

1,219,578
Messages
6,149,098
Members
450,859
Latest member
njaitley

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.

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

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