A Little Help, Please...

[amyacc]

Is there any Easy way to keep #DIV/0! from appearing in cells when there is no matching data to calculate in a formula?

Here's an example of the formula that I'm using...
=SUMIF('Main Sheet'!$Q$7:$Q$176,"In-HouseWonderBall",'Main Sheet'!$I$7:$I$176)/SUMIF('Main Sheet'!$Q$7:$Q$176,"In-HouseWonderBall",'Main Sheet'!$H$7:HH$176)

Again... if there's an easy solution that I can apply to all cells.

THANK YOU!!!!!

 

[Mark W.]

Normally, one would use a formula such as...

=IF(B1,A1/B1,0)

...but, you didn't indicate what your wanted to return instead of #DIV/0!

 

[Aladin Akyurek]

On 2002-05-09 11:07, amyacc wrote:
Is there any Easy way to keep #DIV/0! from appearing in cells when there is no matching data to calculate in a formula?

Here's an example of the formula that I'm using...
=SUMIF('Main Sheet'!$Q$7:$Q$176,"In-HouseWonderBall",'Main Sheet'!$I$7:$I$176)/SUMIF('Main Sheet'!$Q$7:$Q$176,"In-HouseWonderBall",'Main Sheet'!$H$7:HH$176)

Again... if there's an easy solution that I can apply to all cells.

THANK YOU!!!!!

Would what follows be OK with you?

=SUMIF('Main Sheet'!$Q$7:$Q$176,"In-HouseWonderBall",'Main Sheet'!$I$7:$I$176)/MAX(1,SUMIF('Main Sheet'!$Q$7:$Q$176,"In-HouseWonderBall",'Main Sheet'!$H$7:HH$176))

That's, if the second SUMIF result is never > 0 and< 1.

Aladin
This message was edited by Aladin Akyurek on 2002-05-09 12:14