Hide #div/0 error

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

Hi,

Try this:

=IF(ISERROR(your formula),0,your formula)

HTH,

SamuelT

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

See attached. Seems to work fine.

SamuelT

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

Originally Posted by SamuelT

See attached. Seems to work fine.

SamuelT

Thank You Sir

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)

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?

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)

Originally Posted by daddylonglegs

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)

No your formula doesnt work once the table is populate it just gives 0 throughout. Its half way there and i could make do with it. But its not far off. If you have any other ideas then fantastic. I'll contiune to play with it and see what i can do.

OK, I see, formula would work except your percentages derived from VLOOKUP formula are text formatted, you can fix very easily by changing the , in SUMPRODUCT to a *, i.e.

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

Originally Posted by daddylonglegs

OK, I see, formula would work except your percentages derived from VLOOKUP formula are text formatted, you can fix very easily by changing the , in SUMPRODUCT to a *, i.e.

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

Hmm i just tried the new formula and still no success. Have you tried it out in the attached spreadsheet and got it to work?

Originally Posted by daddylonglegs

OK, I see, formula would work except your percentages derived from VLOOKUP formula are text formatted, you can fix very easily by changing the , in SUMPRODUCT to a *, i.e.

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

Oh no its working now!

Cheers Sir

Ok, Last question.

In the summery the Percent Completed also shows some #DIV/0! and i cant use any formulas to get it to display 0.

any ideas?

In Summary G8 you have

=AVERAGE(G6:G7)

I wouldn't use any average formulas here, otherwise you are averaging averages which is mathematically unwise. Try using this formula in E6, replicated for all other rows including totals like G8

=IF(E6,F6/E6,0)

Originally Posted by daddylonglegs

In Summary G8 you have

=AVERAGE(G6:G7)

I wouldn't use any average formulas here, otherwise you are averaging averages which is mathematically unwise. Try using this formula in E6, replicated for all other rows including totals like G8

=IF(E6,F6/E6,0)

Cheers!!!!!!