Don't display zero values

I have a column of data that I'd like to visualize in a line chart. The cells in the column contain formulas that calculate numbers added to other sheets every month. The adjacent column has the names of each month. New data appears when the data for the month is added.

I'd like to ask the chart to not display the values that are currently zero, because we haven't arrived at that month yet. So, for instance, the cell in the column for November 2011 has a zero, because the range it sums has no data in it yet.

Is there a way to do this without having to go back to each chart every month and change the range to add-in the new data?

Thank you, very much, for any help!

alter your formula to output NA() instead of what I assume you are currently using which is ""

Use CF on the cells to hide the #N/A value if that is an issue.

not sure what that formula is meant to mean.

Can you post example workbook of what you currently have.

Why don't you create the line chart from data that indirectly references your chart? By creating a vlookup table that references your data, it will only pull data where the month is entered in table one on my sample and in turn your cahrt will update and include that month in the lines. The dynamic ranges are stored in the name manager which you can find on the data tab, once created, in the Select Data dialogue on the chart select the apppropiate name range Hopefully this makes sense once you see the sample

formula in Totals!C3 , then copy down.

=IF(Apples!$B2="",NA(),Apples!$B2)

See the other reply about dynamic named ranges.

Or see this

No idea. You seem to have spun off on a tanget.

The Vlookup is wrong because the original range it applied to has been checged, the 3 should refer to column 3 but it's now refering to a single column. I'm having a look at the sample now

Originally Posted by scottylad2

The Vlookup is wrong because the original range it applied to has been checged, the 3 should refer to column 3 but it's now refering to a single column. I'm having a look at the sample now

Thank you - and this is very helpful; I've not used the vlookup function yet.

In the end, the chart would ideally display only the data of the totals that are now in the "Current Data" column, not the individual values for Apples, Oranges, and Pears. I appreciate your help, and I'm learning a lot from this!

I think you're maybe not too familiar with Dynamic named ranges, well worth a read up on them. In the sample i'm attaching, the sheet marked Totals will house your DISPLAY data, ie your chart and the tanle the chart is referencing and also a summary of the data thats being collected from the INPUT taps, Apples Oranges and Pears. If you go into the Apples tab, hold control and tap F3 on your keyboard, when the name dialogue box pops up, highlight the word Apples, then at the bottom of the box click on the checkered pattern at the right hand side and you will see what part of the data is being refered to in the dynamic range.

Come out of that then enter some data next to November and do the same, you'll see it's now including that data

The same happens in the orange and Pears tabs, as soon as you add data in any of the Fruits Tabs it gets added to the Totals Tab in table that shows your Fruits. The green box now references to that to hold the data for the chart

fel free to ask if theres a part your not understanding

On the chart, the new month is now visible also. You only need input into any of the tabs marked as fruits, never into the Totals tab

I may have edited this since you last looked

Thanks for the feedback, glad to help

Can anyone suggest a way to represent months of the year in a Dynamic Name range, when I anticipate (or already have in my data), many years? The problem is that a list like:

Please Login or Register  to view this content.

...repeats the values every year. Ideally, I'd like to represent the year next to the January label for each year.

Also, how should I have the values formatted? In other words, should I use the "date" custom formatting, or "General", or "text"? What would be best for Dynamic Name ranges?

Can you clarify, is your Data set up in one continuous column of Dates? ie A1:A100 will that be a bix of months and years so you might have jan 2001 data in the same column as jan 2002?

a fresh sample would help

I'm not sure how you could seperate the months in that fashion and then use them in a chart or any meaningful sum because only one year and manth are tied, the rest are just text entries and october is october, if you don't keep it as a date with a year beside then I'm not sure how you can seperate one year from the other.

But some of the Gurus on here might. I advise you start a new thread, with a sample attached of what you want and also what you want to do with the data, ie if it;s for using in a chart or other calculation. I'm off out now but will look on later

I posted an answer to this last night but it doesn't appear here, maybe I added to the wrong post....ooops!

The Formula in G needs entered with Ctrl + Shift + Enter, not just enter

Extend your ranges to much further than your ever likely to need, if you think your data will fill a max of 500 rows, set the Counta to be looking at 1000 rows. Do that rather than whole column refs, calculation times can be affected using whole column ref's imho

CF is Conditional Formatting.