Help on zeros and charting (I searched)

pete234

Active Member
Joined
Feb 10, 2004
Messages
419
Hi,

I searched and nothing quite fit. I have 12 sheets, one for each month. I have a chart sheet and a table sheet that directly reference these sheets and the totals.

Totals are calculated as I enter data for the months. I also want the charts to calculate on the fly as the totals are entered.

For months that haven't been entered there is "0" for their totals. So January looks good then Feb (since I haven't started yet) the line just plummets to the zero and plots across. I want months that haven't been completed, not to chart. Or if you want, that are zero values, not to plot. It doesn't matter to me how it has to be done. Just have the line end at the month prior to the zero value.

And no, I really don't want to change the X or Y range every month for 14 different charts, although I guess that's a possibility.

I've tried changing the Cell Format to show a Blank if the cell contains a zero. The cells show blank but the chart still plots the zeros. It does this even if I change it on the orginating worksheet and the Chart Tables worksheet. I tried hiding the rows containing the zeros. Unfortunately, that's either my X or Y axis for the chart so it hides my months or Regions on the charts. That won't work. Finally, hiding the worksheets (would make sense!) doesn't hide the cells that are in the plot (?), so they will not make use of the "plot visible only" under Tools > Options. Let me know if you have some input and hopefully I'm overlooking something obvious.
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

Replace the unfilled months with the formula =na(). This will give the #n/a error, but the graph will not show it.


Tony
 

pete234

Active Member
Joined
Feb 10, 2004
Messages
419
I have SUM in the ref cells so I tried:

=IF(SUM(B24,B26,B28)=0,"NA",(B24,B26,B28))

Is that what you mean? This still plotted Jan- Feb with a line. An example would be great.
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Heya Pete,

Replace "NA" with NA() and you'll be good to go.

=IF(SUM(B24,B26,B28)=0,NA(),(B24,B26,B28))

HTH,

Smitty
 

mrovich

New Member
Joined
Sep 21, 2005
Messages
2
NA() not working?!

Hi there! I appear to be having a problem with this solution - my x-axis values are #N/A but they are still appearing in the chart - as "#N/A" !!

Any suggestions on how to solve this same problem with a different solution?

Thanks!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,554
Messages
5,596,814
Members
414,104
Latest member
imamalidadashzada

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top