Plot graph and calculate average from the same ran

Question

Excel graph will not plot null cells and NA() (#N/A). Blank cells (="") for some odd unknown reason will plot as zeros! On the other hand, AVERAGE ignores both, null and blank cells! It does not consider blank as zeros! Which is the expected right behavior!

Graph:

• Ignore: null, =NA() (#N/A)
• Plot as zero: blank (="")    // WHY Microsoft? Why!!!??

AVERAGE(), or any other formula:

• Ignore: null or blank ("")
• Error: =NA() (#N/A), or any other non numeric data!

So, how to use the same column to plot a graph not plotting any non numeric and also calculate the average?

I know that I can just add an auxiliary column to plot, that checks if a value is blank, and replace with =NA(). But, in my case, that will require a lot of auxiliary columns, and I don't want that.

My data comes from formulas. So I can't have null/empty cells, only blank "".

Answer 1

"A lot of auxiliary columns".. 1 is a lot? If data is in column B.

=IF(&B1 = "", NA(),&B1)

And drag it down.

On topic for your problem :

There is an option, change it like that (French sorry):

Change it TO the last choice instead of zeros and it will link your data accordingly.

See this for more information on these options.

Edit 1

If you want to make an Average including N/As:

=AVERAGEIF(A1:A5,"<>#N/A")