Avoid div/0 error?

[MsCynic]

Hi guys, when dragging a cell down to repeat a formula throughout a column, what is the trick that avoids having the #DIV/0! error display on rows without data in them?

Thanks in advance,
C

 

[brian.wethington]

There are many ways to do this. You could do this using an if statement similar to this.

=IF(COUNTIF(

#VALUE!

 

[MsCynic]

Hi Brian, many thanks for replying.

I have two columns of financials but within those column, some rows are blank. Eg:

Jan 06 Jan 07
12000 12222
33099

77000
78900 89000

I am using =(D136-P136)/ABS(P136) to establish the percentage change between January 2006 and January 2007. Naturally if either column has rows without any figure I get the Div/X error, which messes up the spreadsheet.

Cheers
C

 

[Fitzhay]

Hi

You can use the ISERROR Function to test for an error:

=IF(ISERROR((D136-P136)/ABS(P136)),"",(D136-P136)/ABS(P136))

So if there is an error in the calculttion it will return nothing as per the "" or do the calculation if there is no error.

Hope this helps

 

[Aladin Akyurek]

Hi Brian, many thanks for replying.

I have two columns of financials but within those column, some rows are blank. Eg:

Jan 06 Jan 07
12000 12222
33099

77000
78900 89000

I am using =(D136-P136)/ABS(P136) to establish the percentage change between January 2006 and January 2007. Naturally if either column has rows without any figure I get the Div/X error, which messes up the spreadsheet.

Cheers
C

=IF(COUNTBLANK(D136,P136),"",(D136-P136)/ABS(P136))

 

[Stormseed]

Hi

You can use the ISERROR Function to test for an error:

=IF(ISERROR((D136-P136)/ABS(P136)),"",(D136-P136)/ABS(P136))

So if there is an error in the calculttion it will return nothing as per the "" or do the calculation if there is no error.

Hope this helps

You can even specify characters in between "" if you want to display a particular value when the calculation has an error :wink:

 

[MsCynic]

Thanks!

 

[Scott Huish]

Naturally if either column has rows without any figure I get the Div/X error, which messes up the spreadsheet.

Based on your formula, it's not either column that is causing the #DIV/0!, It's what you are dividing by that is causing it.

What is causing #DIV/0 in this case is P136 as this is the denominator in your formula

For just checking if you are actually dividing by 0 or not:

=IF(P136,(D136-P136)/ABS(P136),"")

which means if P136 has a non-zero value go ahead and do the division, otherwise return blank.

Aladin:
I don't believe you can use non-contiguous ranges with COUNTBLANK

 

[Aladin Akyurek]

...
Aladin:
I don't believe you can use non-contiguous ranges with COUNTBLANK

I see a presupposition does necessarily hold...

I thought the OP is after something like...

=IF((N(D136)=0)+(N(P136)=0),"",(D136-P136)/ABS(P136))

 

[Giordano Bruno]

I hesitate to try to add anything in this exaulted company, but here is what I sometimes use, which avoids the use of the "IF" function.

=((P136<>0)*(D136-P136))/ABS(((P136=0)+P136)). It returns a zero in the event that P136 is a zero.