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

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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.
 

Hlatigo

Well-known Member
Joined
Jun 3, 2002
Messages
677

ADVERTISEMENT

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?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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)?
 

Hlatigo

Well-known Member
Joined
Jun 3, 2002
Messages
677

ADVERTISEMENT

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?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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.
 

Hlatigo

Well-known Member
Joined
Jun 3, 2002
Messages
677
you are very much right. i am going to test my data again and see where I went wrong.
 

Forum statistics

Threads
1,143,915
Messages
5,721,520
Members
422,369
Latest member
redinator

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
Top