Problem: Using a radar chart in Microsoft Excel, when missing the first (top) data point interpret between the two adjacent ones.
When the missing value is not the first value you can tell excel to ignore it (hidden and empty cells settings) or use the =NA() function and in either case it will automatically draw a straight line between the two values.
However, when it's the first data point in the series (top in the chart) for some reason Excel doesn't interpret between the two adjacent data points.
If this were a regular, linear, X-Y scatter graph this problem would be easy and we would just average the two data points. But because the radar chart is a polar coordinate system an average between two data points still puts a kink in the line between the two adjacent data points.
When the missing value is not the first value you can tell excel to ignore it (hidden and empty cells settings) or use the =NA() function and in either case it will automatically draw a straight line between the two values.
However, when it's the first data point in the series (top in the chart) for some reason Excel doesn't interpret between the two adjacent data points.
If this were a regular, linear, X-Y scatter graph this problem would be easy and we would just average the two data points. But because the radar chart is a polar coordinate system an average between two data points still puts a kink in the line between the two adjacent data points.
One solution was to just manually draw a line between the two open points, but this was time consuming, tedious and more importantly, not the exact/correct solution to the problem. So instead of paying attention in class I whipped out some law of sines and law of cosines to find an equation which would calculate the value required to put a point on a line between two adjacent points on the spokes of the radar chart if the angle (based on the number of spokes) and the two adjacent point values are known.
Here's the equation and the resulting complete graph.
=Value_A*SIN(ASIN(Value_A*SIN(PI()*4/N_Spokes)/SQRT(Value_A^2+Value_B^2-2*Value_B*Value_A*COS(PI()*4/N_Spokes))))/SIN(PI()-ASIN(Value_A*SIN(PI()*4/N_Spokes)/SQRT(Value_A^2+Value_B^2-2*Value_B*Value_A*COS(PI()*4/N_Spokes)))-PI()/6)
In case you're curious, I used this for the Competing Values Framework (CVF) radar charts.
what is N_spokes
ReplyDeleteN_spokes is a variable (in this case a named cell) for the number of spokes in the graph.
DeleteThanks chad, I tried your formula in my project. But it was failed to interpolate the value. I don't know why. The variable almost similar with your example. would mind to help me?
Deletein your example, Value A is Mentor and Value B is Innovator, while N spokes are 10? am I correct?
ReplyDeleteYes, I believe that's correct. Unfortunately I can't find the original excel file, otherwise I would have posted it for you to reference or confirmed the formula matched.
Deletethank you I appreciate your concern . This blog is only site that explain this subject.
Delete