Having a Chart ignore blanks

[slam]

I'll start by saying I'm not good with charts in or outside of excel, so I hope I'm explaining this accurately.

I have a line chart that I want to ignore blanks from =IF(ISBLANK(),"",) formulas

It's plotting from the range AF2:AY37. AF and row 2 are the X and Y axis respectively, so AG3:AY37 is the data to be plotted.

There is currently only data in columns AF and AG, and in rows 3 through 18. How can I have it ignore the other cells?

For instance, the first value for one line to be plotted is 25, the second is 50, but with the third column being blank, its treating it like a 0. I just want the line to stop on the graph until the next value is entered/calculated.

 

[VoG]

Maybe like this

=IF(ISBLANK(),NA(),)

 

[slam]

Maybe like this

=IF(ISBLANK(),NA(),)

In each of my data cells, or as part the chart source data field?

 

[VoG]

For the cells within the range that you are plotting.

 

[slam]

Yeah, I thought so.... Unfortunately, its for the cells you just helped me with in another thread: http://www.mrexcel.com/forum/showthread.php?p=2873310#post2873310

So they currently contain:

=IF($F$3="","",SUM($E$3:$F$3))

 

[VoG]

Try

=IF($F$3="",NA(),SUM($E$3:$F$3))

 

[slam]

Excellent. That is ignoring the N/A values in AG3:AY37. Thanks!

Can I also do something to have it not plot (or show in the legend), the blanks (or N/A's if I tried that formula again) in AF3 through AF37?

 

[VoG]

You should be able to use a similar formula.


To remove a legend entry http://www.andypope.info/charts/deletelegendentry.htm

 

[slam]

You're right - its not plotting the values. They're just showing up in the legend.

The issue with that solution is that additional values will be added in column AF, so I wouldn't want to be manually deleting anything from the chart legend. I need it to be dynamic.

 

[VoG]

Sorry, I don't know how to hide a legend entry apart from maybe having as formula in the cell with the legend text

=IF(COUNT(AF3:AF37=0,"","My Legend")