I'm trying to create a chart that auto updates itself from a data provided. The range of the chart is always fixed/locked. The issue I'm facing is that when a value for a certain month is 0, I want it to skip to the last non-zero month . Meaning the ranges selected forming the chart should exclude the month with the value zero. Which in most cases is the top month.

The image below should clear it up. enter image description here

Is there a way to have the chart range be dynamic based on certain values?

  • 1
    It may, but I have 20 other columns that some have December data and some that don't. So there's still a lot of manual work. A workaround might help. – andrewk Dec 18 '12 at 21:17

Create a dummy column that automagically contains the X (horizontal axis) value (i.e., the date for the row) if the (Y) value is non-zero, and #N/A if the value is zero, as shown below.  Then chart the dummy column.

                                spreadsheet with data from the questionand D1 =IF(B1<>0, A1, #N/A

Note: this will give you errors saying “A formula in this worksheet contains one or more invalid references” just because you have a formula that evaluates to #N/A.  Ignore the errors.

Hmm. If (as you state in your comment) you have other columns with Y values that go against the same X (month) values, you will need a dummy column for each real column.  I suggest that you create them on a different worksheet.  That also solves the problem of hiding the dummy columns without Hiding them (because, if you Hide them, that messes up the chart).

  • this is a clever workaround. I'm playing with it and getting closer. This method solves one part. Is there a way to totally hide/not show up the #N/A cells. That way I can have a chart range fixed on say the top 12 cells and won't have to redraw the range in the future if say January becomes #N/A. – andrewk Dec 20 '12 at 19:10
  • @andrew: Excuse me, I have cobwebs in my head today. I don’t understand your question. Can you please rephrase it? – Scott Dec 20 '12 at 19:18
  • Sure Scott. Thanks a lot btw. A picture may speak a thousand words. Take a loot at this: top part is current, bottom is desired: i.imgur.com/yLyTg.png Thanks again. – andrewk Dec 20 '12 at 23:12
  • @andrew: Oh, I understand. The only thing that comes to mind immediately is a bit of a kludge: As shown in my answer, define D1 to be =IF(B1<>0, A1, #N/A) and drag (fill) it down to D12. Then define D13 to be =IF(B1=0, A13, #N/A) and drag (fill) it down to D24. Then define the chart to be based on all 24 rows. (If you can guarantee that you will have only one zero, then just define D13 (don’t drag/fill) and define the chart to be based on the first 13 rows.) – Scott Dec 20 '12 at 23:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.