Line chart to ignore blank cell values with formulas

[Robert_G]

I have a line chart based on the months of the year and performance goals with a formula. I want the line graph to ignore the cells with the formula, until that month actually has data collected. That way if I have data plotted for January through July, August through December wont be plotted as a zero.

 

[rilzniak]

Write a formula where the value returns #N/A and the chart will exclude those values.

<month(b1),na(),xyz)[ code]
<month(b1),na(),xyz)<month(b1),na(),xyz)< month(b1),na(),xyz)[="">Ex: =IF(MONTH(DATE(2018,7,31))<=MONTH(B1),NA(),xyz)

B1 = date cell
xyz = whatever your calculation may be for the chart</month(b1),na(),xyz)<month(b1),na(),xyz)<></month(b1),na(),xyz)[>

 

[Robert_G]

=IF(((P50-N50)/P50)/R50="""", NA(), (((P50-N50)/P50)/R50))

I placed an IF statement in my equation and if the cells are blank, it still returns as #DIV/0




What do I have wrong in my statement?

 

[rilzniak]

A couple things, by the look of it.

1) Your math equation - (P50-N50)/P50)/R50 - wouldn't yield a blank, so the NA() would never get triggered;
2) """" isn't proper in Excel (it is in VBA), so that should change;
3) What values are contained with P50, N50, and R50? Without that information, it's difficult to help ensure your formula works perfectly.

 

[Robert_G]

Thanks, by explaining the obvious I didn't realize, I solved my problem. The double "" wasn't the mistake (although it was in a different way), it was as you pointed out it wouldn't yield a blank and the NA wouldn't be triggered.

So i just did this IFERROR statement (since that what it is), and then the chart stopped recording the months that are blank but leave the remaining dates idle in the chart waiting for new data.

=IFERROR(((P50-N50)/P50)/R50,NA())