MrExcel Publishing
Your One Stop for Excel Tips & Solutions

If Statment??


Posted by Paul Magruder on August 10, 2001 8:03 AM

I have a line chart with a data range consisting of formulas. Occasionally there is no data for the formula to calculate and the chart interprets the formula as having a value of zero. As a result the chart becomes confusing because of the appearance of vertical lines and not knowing if the value is truly zero or if no data was available. I have tried an IF statement that returns "" if no data exists but because it is a formula it is still interpreted as having a value of zero. Is there a way to write a statement that returns Null? These vertical lines are driving me crazy!!

Thanks in Advance
Paul


Posted by Barrie Davidson on August 10, 2001 8:41 AM

Paul, if you don't want to graph that data point then you need to return the NA function in your IF statement. So your IF statement would be(assuming if the condition is true you have no data to chart):

=IF(Condition,NA(),Your Data)

Regards,
Barrie

Posted by Ian on August 10, 2001 8:41 AM

=if(a1=0,#n/a,yourcalc)...conditional format the #n/a to font white if needed