ISNUMBER function...how do I use it?

Hlatigo

Well-known Member
Joined
Jun 3, 2002
Messages
677
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

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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)?
 
Upvote 0
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)

What is your thought?
 
Upvote 0
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)

What is your thought?

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.
 
Upvote 0
you are very much right. i am going to test my data again and see where I went wrong.
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

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