How do I get radar graphs to not plot empty cells?

[SimonR]

I'm writing a macro that will allow users to input data and eventually the
macro will generate a radar graph. Different users will have a different
number of data points for the radar graph based on their input. When I setup
a radar graph to chart data from a certain range, I want the graph to only
plot cells with actual numbers in them and ignore empty cells. The options
under 'Tools->options->charts->plot empty cells as' has not been able to
solve the problem. Thanks for the help.

 

[Andy Pope]

Hi,

Plot empty cells will only work if the cells are truly empty. The spoke
of the radar will not be removed but the line between the adjacent
spokes will be broken.

If the cells contain formula then use NA() to represent empty cells.
This will not cause the line to break but instead the line line between
adjacent points is plotted and the marker is not.

Cheers
Andy

 

[SimonR]

Thanks for the reply. I'm going to have up to 20 cells that can be filled to
make this radar chart, and they will be filled from top to bottom with no
gaps in between. So if I have only 5/20 cells filled, is there any way for
the radar chart to know to make a pentagon shape, or will it always have to
make a 20 sided shape and have 15 empty spokes? Thanks again.

Simon

 

[Andy Pope]

Hi,

In that case you can use dynamic named ranges to automatically
expand/contract you chart.

For explanation see here
http://peltiertech.com/Excel/Charts/Dynamics.html
http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/index.html

Cheers
Andy

 

[SimonR]

Thank you! This is exactly what I've been trying to figure out for weeks.