Ignore #N/A in chart AND growing average formula

[Kimber]

I have a report that includes an accuracy column. A second column calculates the average accuracy for month 1 through the current month. In order for the chart to display (or not display, actually) the months with no data, I've entered "#N/A." The charts work properly for the monthly accuracy number. However, I have tried to modify the average accuracy formula in several ways to ignore the #N/A, but I cannot get it to work properly. It seems that I can either get the formula to work by ignoring the #N/A and the chart is confusing or I can get the chart to look appropriate but the calculated running accuracy average column does not work. Please help!

 

[GraH - Guido]

This perhaps in cell C2?
=AGGREGATE(1,6,Table1[[#Headers];[Accuracy (%)]]:[@[Accuracy (%)]])

1=average
6= ignore errors

 

[GraH - Guido]

What happens if you just delete the textual #N/A?
and perhaps use =IF(OR(B2<>"",B3<>"",ROW()=2),AVERAGE($B$2:B2);NA())

EDIT: =IF(OR(B2<>"",B3<>"",ROW()=2),AVERAGE($B$2:B2);NA())

 

[Kimber]

This perhaps in cell C2?
=AGGREGATE(1,6,Table1[[#Headers];[Accuracy (%)]]:[@[Accuracy (%)]])

1=average
6= ignore errors

Thank you so much! This formula seems to beautifully with the exception of #N/A in the first row (B2.) In this case (I've uploaded the sheet to reflect same) an error is returned. I cannot delete the #N/A because both columns are charted. Again, the problem arises when #N/A is in the first row.

 

[GraH - Guido]

Not sure I follow on "cannot delete the NA", since I did it and it had no impact on the column chart. You need #N/A not to plot when it is a line chart. And this is actually what is happening on your chart.
The N/A I find a bit disturbing are the labels "N/A" on the chart. You could add a duplicated column "labels" and have them replaced by "". Plot this also on the chart with 100% overlap, make the columns fully transparent (no color, no border) but use the labels.
Could this work for you?

 

[Kimber]

Not sure I follow on "cannot delete the NA", since I did it and it had no impact on the column chart. You need #N/A not to plot when it is a line chart. And this is actually what is happening on your chart.
The N/A I find a bit disturbing are the labels "N/A" on the chart. You could add a duplicated column "labels" and have them replaced by "". Plot this also on the chart with 100% overlap, make the columns fully transparent (no color, no border) but use the labels.
Could this work for you?

Hi again, G. I had unselected "Show #N/A as an empty cell" so there is no label for those cells now. Please see the attached edited file and note the error in C2. Thank you for your patience as I try to keep up with your suggestions!

 

[GraH - Guido]

Did you check my last file? The error in C2 is gone there.
=IF(B2="",NA(),IFERROR(AGGREGATE(1,6,$B$2:B2),NA()))

So whether the data is not filled in yet or whether the average returns an error, it will result in #N/A, which is not plotted on the line chart.

Nice that "unselected "Show #N/A as an empty cell" is working. I thought I tried it also, but that it was not working. Probably I did something wrong myself. that happens too, you know. Or we are not talking about the same thing. Nevertheless, you almost have the result you want.

 

[Kimber]

Ahhhh, I did not download your file. My bad. I have used a label column in other tables and charts and that is the perfect solution. Thank you, G, for your timely, efficient, and effective assistance!
Gratefully,
k

 

[GraH - Guido]

Ahhhh, I did not download your file. My bad.

I have used a label column in other tables and charts and that is the perfect solution. Thank you, G, for your timely, efficient, and effective assistance!

So glad you liked it!