Get rid of #DIV/0 in an array formula?

[Phutile]

Here is my current formula:
{=STDEV(IF(MixRange=P5,lbshrRange))}
The only problem that I have with it is it returns #DIV/0 if there is a count of 0 or 1. I am guessing that I can use ISERROR for this so that the #DIV/0 doesn't show, and a "" shows instead. Can you rewrite the formula so that it will not give an error message? I'm still learning some of these logical formulas. Thanks.
This message was edited by Phutile on 2002-08-27 13:53

 

[Juan Pablo González]

You could use an IF condition, as

=IF(COUNT(lbshrRange)>1,yourformula,"")

 

[Phutile]

i wish that would work...but the value has to match up with the mixrange first...that way it only takes the std dev of those values. The count isn't needed within the formula, i just know that when it matches up with the mixrange that there is only 1 or 0 values. Understand? Kind of wierd.

 

[Aladin Akyurek]

On 2002-08-27 13:52, Phutile wrote:
Here is my current formula:
{=STDEV(IF(MixRange=P5,lbshrRange))}
The only problem that I have with it is it returns #DIV/0 if there is a count of 0 or 1. I am guessing that I can use ISERROR for this so that the #DIV/0 doesn't show, and a "" shows instead. Can you rewrite the formula so that it will not give an error message? I'm still learning some of these logical formulas. Thanks.
This message was edited by Phutile on 2002-08-27 13:53

Either, array-entered,

=IF(COUNTIF(MixRange,P5)>1,STDEV(IF(MixRange=P5,lbshrRange)),"")

Or, if you have the morefunc add-in installed, array-entered,

=IF(ISNUMBER(SETV(STDEV(IF(MixRange=P5,lbshrRange)))),GETV(),"")

 

[Phutile]

Thanks Aladin...seems that you always have an answer. I'm actually learning quite a bit from your help. Thank you.