Omitting Radar Chart Axes

[Legacy 219053]

Hi,

I'm excited to have found this site and look forward to learning from it for years to come. I am a relatively new Excel user and have a question about radar charts (or spidergrams as I have sometimes seen them called.)

I want my information to be shown using a radar chart that may at times have up to seven axes. Sometimes, there are not always values for every axis and the radar chart plots these points as zero. Is there a way to design my chart to where if one of my values that is to be graphed is a zero or an error, then Excel will omit that axis and instead construct a radar chart without that axis?

I don't want to falsely represent these values as zero (when the problem is missing data) and omitting them would make the chart look so much cleaner. Any help with this would be much appreciated.

 

[Steve=True]

Hello Crazy horse and welcome to the forum!!

I see that you have 2 options.

1) you can delete the entry / leave the data blank and this will show a Gap for that axis point.

2) you can use the N/A function and this will not plot a point for that

Here is an example of using an NA() function in a formula to hide data points in graphs:
How-to Plot Building Wall UR Value in an Excel XY Scatter Chart
http://www.exceldashboardtemplates.com/?p=1602

HOWEVER, if you use the NA() Function in a radar graph, it may misrepresent your data becuase although the line doesn't break, it now appears as though there is a data point at that axis point because the line crosses it axis. Check out this picture to see what i mean.

Please let me know if this helps.

thanks

Steve=True

 

[Legacy 219053]

Thanks for your help Steve. Unfortunately, I needed an alternate solution due to the fact that there are a possibility of eight axes and the average user would only have inputs for three to five of them. Having that much empty space or having the line travel through the axes where there was no value reported would not be acceptable. Here is what I did instead.

I assigned a macro to a button that says "View Own Scenario"

Sub ViewOwnScenario()
'ViewOwnScenario Macro
Sheets("Own Graph").Select
Dim c As Range
For Each c In Range("h5:h11")
c.Rows.Hidden = IsError(c.Value)
Next c

End Sub


This directs me to another page which at first contains a table with all eight data points, but then hides the rows that don't have a value (which I have made NA()). I know this is a slow and somewhat dirty way to go about it, but it works. This is my first time using macros and VBA, please feel free to critique. Do you foresee any problems with this solution?

-Crazyhorse

 

[luisgutierrezr]

Hi...

¿What if the missing value is in the first item of the list?
(The january one on your example)
¿Does the line connection between december and february gets created?

 

[Steve=True]

Hi Luisgutierrezr, you will need 2 points to connect and display a line within a Radar chart. Any single point will not be shown.

 

[luisgutierrezr]

Hi Again!

How would you handle this scenario?

 

[Steve=True]

So if i hear you correctly, you want to add a point to the radar chart that is not showing up because there is not a data point connecting it? If so, 1, i would see if i can rearrange my data to make the chart work without much effort by having all the points at least connected by 2 per section with no single points without a connector. If you can't rearrange your data, then you may have to make a combo chart to show the single data point using the XY scatter chart (not positive you can do this, but that would be the only other option short of drawing it with shapes.)

 

[luisgutierrezr]

Please excuse, it looks the image I tried to share when asking how to handle that situation was not shown...

Here it is:
https://lh5.googleusercontent.com/f...lCay6c_NQu2k4l1YxfZFlp8JUW8dDmPOHI0YASbi=w673

How would you handle this scenario?