Looking for "StDEVIF", if it exists

[hillryane]

Does excel contain a formula operator similar to SUMIF but would utilize
StDEV in place of SUM.

I need StDEV to ignore 'empty' formulas in my sheet which return
#DIV/0!

Examples:

STDEV(A2:A7)
A2:A7 contain formulas

Problem is, only A2,A4,A5 contain 'real' number results, the rest are
empty formulas. If I use STDEV(A2:A7), the remaining '#DIV/0!'s in
A3/6/7 throw off the calculation and return a #DIV/0!

In a similar instance, I had problem SUMming a column full of
calculated and empty formula results. I used SUMIF:

SUMIF(A2:A7, ">0",A2:A7)

Worked fine.

Any ideas on the "STDEVIF" or ways of replicating it. Thanks

 

[Harlan Grove]

Does excel contain a formula operator similar to SUMIF but would utilize
StDEV in place of SUM.

I need StDEV to ignore 'empty' formulas in my sheet which return
#DIV/0!

Examples:

STDEV(A2:A7)
A2:A7 contain formulas

Problem is, only A2,A4,A5 contain 'real' number results, the rest are
empty formulas. If I use STDEV(A2:A7), the remaining '#DIV/0!'s in
A3/6/7 throw off the calculation and return a #DIV/0!

In a similar instance, I had problem SUMming a column full of
calculated and empty formula results. I used SUMIF:

SUMIF(A2:A7, ">0",A2:A7)

Worked fine.

Any ideas on the "STDEVIF" or ways of replicating it. Thanks

 

[Harlan Grove]

Does excel contain a formula operator similar to SUMIF but would utilize
StDEV in place of SUM.

No, but the array formula STDEV(IF(condition,range)) would work (if necessary).

I need StDEV to ignore 'empty' formulas in my sheet which return
#DIV/0!

The only times STDEV returns #DIV/0! is if some of the values fed to it are
#DIV/0! themselves or if you give it fewer than two numbers to work with. On its
own, STDEV happily ignores cells containing anything other than numbers.

Examples:

STDEV(A2:A7)
A2:A7 contain formulas

Problem is, only A2,A4,A5 contain 'real' number results, the rest are
empty formulas. If I use STDEV(A2:A7), the remaining '#DIV/0!'s in
A3/6/7 throw off the calculation and return a #DIV/0!

...

What exactly are the contents of A2:A7? If A2, say, were a text string that just
appears to be a number, STDEV won't process it. If all your 'real' numbers are
actually text strings, STDEV properly returns #DIV/0!. Does =COUNT(A2:A7)
return 3?