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

Sliding chart with option to freeze starting point at desired datapoint

kuldeepjainesl

kuldeepjainesl

Member
This chart gives me a window of sliding 9 months. Now I want to add a check box on the chart "Freez start date" so that if it is checked, chart should not be sliding anymore and should accommodating new data point in it.


For example: Charts is showing "Jun-12 " as starting date hence would be ending on Feb-13. Now if user check the box then Jun-12 should be fixed and moving scroll bar should now accommodate the date point in it from Feb-13 onwards giving a more month data in chart window.


Sample file is available with sample data at : http://sdrv.ms/Spy3vU
 
Interesting question KPJ...


While this is possible with formulas, we need use circular references. We need to extract a subset of data based on the freeze & scroll bar values. The logic is like this:


When the freeze option is unchecked,

- use the scroll bar position as start of data series

- end of series will scroll bar position + 8


When freeze is checked

- copy the current scroll bar value in to another cell. This cell will remain unchanged until you unfreeze. This is where circular references come in to picture. (more on circular refs)


- use the copied position as the start of data series

- end of series is start + 8 + (difference of scroll bar value and copied value)


See the attached file for the implementation.


http://img.chandoo.org/playground/Slideing%20chart%20with%20freeze%20option.xlsx


How to use it:


First go to Excel options > Formulas and Enable iterative calculation mode.

Then you can use the chart.


PS: The scroll bar can add or remove points from the chart. It will remove the data if you scroll back.
 
Wow...I am speechless. Solution implemented workbook in just 1 hour. You are truly awesome. Thanks a ton chandoo.


i am building a workbook from last 2-3 months with all the little i know in excel but let me admit that learnt a lot from this forum.


Regards,
 
You need to imagine a situation where you have several points /month to display say from Yr-1990 to Yr-2012, now this much of data you can not analyse in detail if plotted in a small chart window and this make chart boring hence a sliding window was required. while for 75% cases this sliding window is sufficient but in 25% of time there will be need to be increased the window size a bit to get a broader picture hence freezing would help me here.


Hope i am clear now.


Regards,
 
You must log in or register to reply here.
Back
Top