Chart reflecting blank values having formulae

[skarnik01]

Hi,
I have created a Chart with dynamic source (using Name Manager) whose range gets auto-updated on daily basis i.e. using last 250 values in the range.
The problem area is the last few values in the range (of the Chart data) which have a formula but are presently blank due to zero value in the adjacent dependent cells. These cells of the source range currently reflect the value as either - #VALUE! or #REF!
The line Chart thereby drops down to zero for these values. How can I get those rows ignored in my last 250 rows of the Range thereby giving a right picture in my chart?

(Please note - My file gets updated on the basis of the Macros and thereby involve minimum manual intervention)

Thanks in advance,

 

[NARAYANK991]

Hi Karnik ,

A line chart will ignore #N/A error values ( as a result of a formula returning this value , or as a result of using the NA() function ) ; however even if your cells have this error value in them , the X-axis will still show the corresponding values ; if this is acceptable , then you should see if you can use the IFERROR wrapper around your existing formulae to generate the #N/A error values.

Another option is to hide the rows which have the #VALUE! or #REF! error values ; a line chart will skip values on hidden rows , and even the X-axis will not display the corresponding values. This may be a better option , since your file is already a macro file , and introducing a macro which hides rows that have error values in them will not be a problem.

Narayan

 

[Ananda Kumar C]

Hi Karnik,

Is it possible to filter your data to display only non-error values? In that case, the chart will skip the error values. (Technically filter could be on any condition)

Anand