MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Line Graph Problems - It works in Lotus !!!!!!

Posted by Nick Limbrick on October 12, 2000 5:39 AM

I have just switched from Lotus 123 to Excel.

In Lotus I had a line graph generated from a list of formulas.

Some of the formulas give an error message as no data is available for those points and the line graph does not plot the points. Great that's what I want.

In Excel however the formulas give #DIV/0! message and the line grpah treats them as zeros and plots them.

Without running a macro or manually clreaing out the errored formulae is there any way of stopping them being plotted.


Posted by AB on October 12, 2000 7:28 AM

You just need to add some additional error handler logic to the cell formula to test if the divisor is zero. If the condition is true, then return NA() otherwise allow the division.

This should convert your #DIV/0! errors to #NA errors and your chart will ignore them.

Something like this should work:
=IF(B1=0, NA(), A1/B1)

Posted by Nick Limbrick on October 13, 2000 5:27 AM


Thanks you very much you have no idea how many people I have asked about this without finding a solution.