Excel question: If error retun actual empty cel...l

[Trifid]

And not a cell that appears empty but line graphs display it as a zero value.

I've got the following formula which displays the average time spent in a queue per minute

=IFERROR(AVERAGEIFS(D:D,A:A,">="&F10,A:A,"<"&F11),"")

So it displays the average value from column D if call comes in column A between time F10 to F11.

This works great if a call comes in once per minute or more, but if there is no call to sample then it returns an error so I added the IFERROR. If it returns an error I want it to display no value at all and then the line graph looks perfect but this formula, while appearing correct in the calculation the graph displays a 0 value.


I've already got the graph set to connect data points and it works perfectly if I delete the formula in the error cells but not with the complete formula.


Any ideas?

Thanks.

 

[kinobestew123]

http://superuser.com/questions/4803...to-ignore-num-errors-in-plotting-a-line-graph

This seems to explain a few possible options. One being to put NA() instead of "". It will give you #N/A which is annoying but should work. The other is to alter the settings on your graph to ignore zero data.

 

[Trifid]

The NA() is perfect! The actual calculation is hidden away from the viewing part so suits my needs perfectly.

 

[kinobestew123]

Awesome, glad it works.