STDEV ignoring blank cells aswell as transforming negative numbers to positive.

[Per_s]

Hello,

Im trying to look at an column that holds this data:

-170
171
172
-173
174
175
176
177

-178
179
180
180
180
180
180

And then calculate the STDEV from that not counting blanks and ignoring the - infront of some numbers (I want to use the value, just not -)

I tried this, but cant make it work; {=STDEV(VALUE(SUBSTITUTE(E6:E21;"-";"")))+0}

Any idea?

[José Augusto]

Try this array formula
=STDEV.S(IF(ISNUMBER(A3:A18),ABS(A3:A18)))

[Tony Valko]

=STDEV.S(IF(ISNUMBER(A3:A18),ABS(A3:A18)))

The STDEV.S function requires Excel 2010 or later.

[José Augusto]

The STDEV.S function requires Excel 2010 or later.

Thanks Tony. Yes, you are right. STDEV and STDEVP exists in 2010 and further for compatibility mode
So, I suppose the solution is the array formula

Please Login or Register  to view this content.

for Excel 2007 and previous version

[Per_s]

Thanks for your help, worked like a charm!

[José Augusto]

You are welcomed.
Don't forget to mark this thread as SOLVED.