Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
It may be easiest just to hide the blank row until the data becomes available. – pnuts Dec 18 '12 at 20:30
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).

share|improve this answer
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: 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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .