Excel Chart to Ignore Cells with Formulas Which Return #N/A

Hi.

So, i've gone through several iterations of trying to solve this problem. I have a chart pulling data from a range of cells with formulas in them. Some of the cells have formulas which currently return #N/A, but which may in the future return a numerical value. I need a chart which:

1) Does not chart cells which return #N/A
2) Does chart new cells within a defined range once they stop returning #N/A and return a value instead.


I have tried using formulas using na() when the formula is false. This doesn't seem to work.

In addition, I have tried to set a dynamic range, using the countif(range,">0"). Also does not seem to work.

Help! I've attached the workbook.
Ignore NA in Chart.xlsx

Hi Andy -

Thanks for your help.

Unfortunately, when I followed your same structure in the actual sheet (The one I sent you was a copy), I still get an error message. I've attached the actual sheet here. What am I missing?

Jacob

Hi Andy - Thanks for your help. Unfortunately still running into problems. See attached below.

Jacob

Hi Glenn. This is the chart. I need the chart to graph cells EC26-EQ26 on the "Raw Data" tab.

JacobChart Dynamic Range Example 2_6-15-2015.xlsx

Hi Glenn -

So, the problem is that In the range EC26-EQ26, I need the chart to only chart the cells which return numerical values. The challenge being, on some occassions, it might be 6 cells. On other occassions, 10 cells, etc. etc. So I need to chart a dynamic range. I have been unable to, for some reason, set up a named range.

I have tried using formulas using na() when the formula is false. This doesn't seem to work.

In addition, I have tried to set a dynamic range, using the countif(range,">0"). Also does not seem to work.

Jacob

AH! Got it! I had left out the worksheet name when calling the dynamic range in the excel chart. Works perfect now. Thanks Glenn!