Line Graph - Do plot zero, Don't plot #NA

[Janc]

Hi guys

I'm creating some charts to show performance and have different coloured series depending on whether achievement was below 90%, 90-95 and >95%.

The problem I'm having is that I do need to plot zero values, but I don't want to plot #NA values - eg. July is 0%, Aug is 95%, Sept #NA and Oct 56% - I would like the line to miss out Sept and join Aug directly to Oct.

I've attached a simplified version to try and demonstrate the problem and would be glad of any input. I'd also be happy to listen to ideas of how this info could be better presented as I've a feeling it's going to look a bit messy once I add in another 2 full series of data...

Thanks

[Andy Pope]

#N/A suppresses data marker only.

If the cell contains something the line will remain intact.

This may help, if the data suits.
http://www.andypope.info/charts/brokenlines.htm

[Janc]

Thanks for the link, some great stuff on there - but nothing that would help me with this that I could see unfortunately.

My chart seems to be taking the #NA and including it as a 0% data point, but with a >95% marker..

Do you have any other suggestions?

Thanks
Jan

[Andy Pope]

I just checked your file.

You have #NA which is just text so treated as zero.

Use #N/A or the formula =NA()

[Janc]

Brilliant, thanks!

Whoever thought that something so stupid/simple would cause me such a headache!!

[Edgeware]

Great tip. I have been manually leaving cells blank to avoid trend lines dropping to zero. Thanks a lot!

I just checked your file.

You have #NA which is just text so treated as zero.

Use #N/A or the formula =NA()