Average values using criteria

[larryg003]

Hey guys,
I have a problem with one of my data sets
I have 2000 rows which have numbers which negative, positive or zero
How do I average only the numbers that are not zero and divide by the standard deviation of those numbers (not including the zeros)
For example, I have the following values in A1-A2000
-0.1
1.4
0
0
0
3
0
-1
-50
0
all the way to 2000...
I want to average only the cells that are not zero and divide by the standard deviation of those numbers. So I want my formula in b1 to be average(-0.1,1.4,3,-1,-50)/stdev(-.01,1.4,3,-1,-50)

THANK YOU SO MUCH!

[quekbc]

This should work.

Please Login or Register  to view this content.

followed by the ever so important Ctrl + Shift + Enter

[larryg003]

Sorry I forgot one thing,
I also have #div/0 in there several times. How do I also disregard that?

[NBVC]

Where do those errors come from?

You probably would be better off error trapping the formulas in the A1:A2000 range and get it to return 0 instead.

[DonkeyOte]

NBVC is right of course but for the sake of example if nothing else...

=AVERAGE(IF(ISNUMBER(A1:A2000),IF(A1:A2000,A1:A2000)))/STDEV(IF(ISNUMBER(A1:A2000),IF(A1:A2000,A1:A2000)))
confirmed with CTRL + SHIFT + ENTER