Hide #div/0 error

**tompich** · 09-19-2007, 05:01 AM

Anyone know how to get excel to display 0 instead of this? It pops up everywhere through my spreadsheet but cant get it to display 0.

Regards

Tom

**VBA Noob** · 09-19-2007, 05:04 AM

Maybe

=IF(ISERROR(A1/B1),0,A1/B1) or CF

http://www.contextures.com/xlCondFormat03.html#Errors

VBA Noob

**tompich** · 09-19-2007, 05:15 AM

Still no joy.

I have attached the file.

The Summery worksheet is displaying information from the Details worksheet.

So if you look in Details and scroll down to the green column titled Kunal Benpuri, under Bahadur, Shekhar you can see there is no values available in his tables so Cell D107 displays #DIV/0! but i'd like it to display 0 as it makes the Summery worksheet look messy.

Any ideas would be great.

Thanks Again

Kindest Regards

Tom

**VBA Noob** · 09-19-2007, 05:29 AM

Maybe

=IF(ISERROR(SUMPRODUCT(--(C93:C106)*(D93:D106))/C107),0,SUMPRODUCT(--(C93:C106)*(D93:D106))/C107)

or use the Conditional Format idea on the link I provided

VBA Noob

**tompich** · 09-19-2007, 05:33 AM

Hi thanks that works a treat.

I know this is asking alot but would you be able to explain that formula for me. Just so i can understand whats going on where. I love to accept help but i like to understand how things work also just so it helps for future development.

Cheers

Tom

**daddylonglegs** · 09-19-2007, 05:38 AM

To avoid #DIV/0! errors you only really need to test whether the Denominator is zero, therefore you can use this formula

=IF(C107,SUMPRODUCT(C93:C106,D93:D106)/C107,0)

**SamuelT** · 09-19-2007, 05:31 AM

See attached. Seems to work fine.

SamuelT

**tompich** · 09-19-2007, 05:36 AM

Originally Posted by SamuelT

See attached. Seems to work fine.

SamuelT

Thank You Sir

**corinereyes** · 09-19-2007, 05:38 AM

Hi tompich,

Try this also:

On cell D107:

={IF(C93:C106 = 0, 0, SUM(C93:C106*D93:D106)/C107)}

**tompich** · 09-19-2007, 05:45 AM

Hi the only problem with this is that it only works if the table is empty. If i populate the table with figures the figure remains 0. Is there an alternative formula that allows it to work both ways?

**daddylonglegs** · 09-19-2007, 05:48 AM

Hello tompich, which suggestion are you referring to? My suggestion above will give the correct results when numbers are added, I believe, and it doesn't require CTRL+SHIFT+ENTER

=IF(C107,SUMPRODUCT(C93:C106,D93:D106)/C107,0)