• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

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.
 

Attachments

  • TEST ver 2 22.xlsm
    213.7 KB · Views: 15
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).
 
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?
 

Attachments

  • TEST ver 2.22.1.xlsm
    212.6 KB · Views: 5
Hi:

May be this

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

Thanks
 

Attachments

  • TEST ver 2.22.1.xlsm
    210.1 KB · Views: 9
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.
 

Attachments

  • TEST ver 2.22.1.xlsm
    209.1 KB · Views: 44
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.
 
Back
Top