X Axis; Ignore NA() from formulas in chart data

[Seeker After]

Hello All,
I am attempting to use dynamic named ranges (offset functions) to plot data on a line chart. The data has various formulas to process it before it gets to the chart allowing the user to select what data they see from the raw data. The live raw data set is quite large. So to allow for easier viewing, the dynamic chart via the selection criteria has been built.

My central issue is: with the formulas, the chart is including on the X-AXIS blank spaces at the end of the dataset if I use "" in the formulas and #NA if NA() is used. I want to plot only the dates on the X-AXIS, with the formulas in the dataset, and have a dynamic switching chart as you can see in the uploaded file. Can it be done? Thanks a bunch in advance.

 

[Chihiro]

To exclude error, you can replace COUNTA() portion in your named ranges with...
COUNT(CALCS!$E:$E)
Since all your chart ranges contain numeric values.
Replace range as needed.

However, there are columns that return all range as error. For those ranges, you may just have to use date column for count calculation.

Ex: RunRange
=OFFSET(CALCS!$F$8,1,0,COUNTA(CALCS!$F:$F),1)

Becomes...
=OFFSET(CALCS!$F$8,1,0,COUNT(CALCS!$D:$D),1)

Also, you will need to update Axis label range for each of Legend Entries (Series) to be dynamic as well.

You have some as dynamic, but others are fixed range (ex. S-Mean).

 

[Seeker After]

Thanks so much Chihiro. I made the corrections you suggested and a few of my own attempts. There is some improvement however as you can see from the uploaded the chart it still showing #NA on the X-AXIS. What did I miss?

 

[Nebu]

Hi:

May be this

=OFFSET(CALCS!$D$15,,,COUNTIF(CALCS!$D$15:$D$109,"<>#N/A"),1)

Thanks

 

[Chihiro]

One thing you had missed is the ctrlchrtvalues named range.

Formula should be updated to...
=OFFSET(CALCS!$E$8,1,0,COUNT(CALCS!$E:$E),1)

You also had fixed range for few series instead of dynamic.

See attached sample.

 

[Seeker After]

Thanks again Chihiro. I had to just build another chart to make the corrections and eliminate all of the mistakes you pointed out. Now it works with the correct dynamic named ranges for a perfect dynamically switching chart.