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

#### Phutile

##### Board Regular
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

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

#### Juan Pablo González

##### MrExcel MVP
You could use an IF condition, as

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

#### Phutile

##### Board Regular
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.

##### MrExcel MVP
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

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

Replies
0
Views
485
Replies
3
Views
178
Replies
3
Views
99
Replies
6
Views
86
Replies
3
Views
546

1,191,219
Messages
5,985,335
Members
439,958
Latest member
qb0000

### 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?

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