• 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.

Dynamic chart axis

W

wollyka

Member
Hi
I created a Dynamic chart using the usual drop-down box and named ranges. The table to plot contains dates (year and period from 2012 onward) and the values for different sizes.
The excel file attached below is working as expected.
However, my table contains missing values (i used the formula NA() to refer to them) for certain dates. For example, for the size Large, the data starts from year 2013. How can i make the chart to adjust the axis labels automatically to start at 2013 and not at 2012? e.g. how to make start from the first date that has a value and not N/A?
Thanks
 

Attachments

  • Example_Chart.xlsx
    14 KB · Views: 4
We need to have a formula figure out where the first row in selected column that has a number is. In the attached, I created several more named ranges:
myCol - the column of table you want to return
firstRow - What is the first row in myCol with a number?
chtLabels - Takes the above 2 inputs and figures out correct ranges of labels for chart
Chart_Date - Changed definition a little, but gathers correct numbers.

Let me know if you have any questions about the formulas I used. :)
 

Attachments

  • Example_Chart Solved.xlsx
    14.1 KB · Views: 5
Hi
Thanks for the help.
Let me first grasp what you did and have a look on the formula. I will get back to you if i have any questions.
Thank you again
 
Hi
What if i want to include also"Period" next to year to the axis labels? e.g. 2012 H1
Can i do it using or modifying the named ranges or do i have to create a helper column with CONCATENATE?
Thanks
 
Ah, I missed that before. I changed the definition slightly for chtLabels so that it now pulls both columns, and then tweaked Chart_Data to be able to handle the 2 column labels.
 

Attachments

  • Example_Chart Solved 2.xlsx
    13.1 KB · Views: 12
Ok thank you very much
Also, i noticed that your first excel can work, you just need to select the two columns Year and Period in the Horizontal Axis Label In Select Data Source in Chart and voila!
edit:scrap my last comment. If i do that it won't be dynamic.. silly me
 
Last edited:
Ok another question.
If for some reason, one column contains all N/A (no data), i am getting an error in that case. Is something can be done to circumvent this error or at least not display it?
 
We can do that. Open the Name Manager, go to firstRow. Change the definition from:
=MATCH(TRUE,ISNUMBER(INDEX(Av_Index,,myCol)),0)
To this:
=IFERROR(MATCH(TRUE,ISNUMBER(INDEX(Av_Index,,myCol)),0),1)

That says that if we don't have any numbers, just display all the labels (but obviously no data will show due to the N/A).
 
You must log in or register to reply here.
Back
Top