Thread: 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