with data like below
+---------+-------+-------+-------+ | | test1 | test2 | test3 | +---------+-------+-------+-------+ | metricA | -87.1 | -87.3 | -87.6 | | metricB | 12.35 | 12.2 | 12.25 | | metricC | 2.2 | 2.1 | 2.05 | | metricD | 7.7 | 7.9 | 7.8 | | metricE | 3.61 | 3.36 | 3.48 | +---------+-------+-------+-------+ I'm trying to create a radar chart in excel - I get the following chart - however since the values are very close the results from the three tests are overlapping each other. How do I adjust the axis limits such that the differences are displayed in the chart ? I was able to change it only for one axis - the one corresponding to metricA.
6 Answers
Answers 1
You didn't specify the application domain, so I don't know what these numbers mean.
The first best solution is what others also wrote: to change the range of values.
A) Differences instead of absolute values (if the essential question is their difference).
B) Ratios. In other cases their ratio to each other or to the average of the group or to an external standard value is more important, like the industry standard is -85, so test1 is at 102% - this differences will not be bigger but all metrics will have the same data range, so the scale can be adjusted to show the differences better.
C) Compare to an industry average and a standard deviation (e.g. test1 is 2.5 sigma from the standard regarding metricA)
The second best solution is to use a clustered column chart or multiple charts.
The third best solution is to improve somehow this radar thing, and make visible that all the three are essentially at the same place. For this, you can change the thickness and style of the lines (as below) or of the markers.
Answers 2
To compare the three tests without fiddling with the axis scales, you can try some sort of standardization - I got an OK result with subtracting the difference between a test score and the mean average of the test scores for that metric e.g. =B2-AVERAGE($B2:$D2)
So if your test data is in B2:D7 like this:
test1 test2 test3 metricA -87.1 -87.3 -87.6 metricB 12.35 12.2 12.25 metricC 2.2 2.1 2.05 metricD 7.7 7.9 7.8 metricE 3.61 3.36 3.48 Then put that formula in and copy across and down to get:
test1 test2 test3 metricA 0.2333 0.0333 -0.2667 metricB 0.0833 -0.0667 -0.0167 metricC 0.0833 -0.0167 -0.0667 metricD -0.1000 0.1000 0.0000 metricE 0.1267 -0.1233 -0.0033 Gives this chart:
Other formulas I tried with:
=(B2-MIN($B2:$D2))/(MAX($B2:$D2)-MIN($B2:$D2))which is a normalization giving a number between 0 and 1=STANDARDIZE(B2,AVERAGE($B2:$D2),STDEV.P($B2:$D2))which leverages Excel'sSTANDARDIZEfunction.
Answers 3
Instead of displaying the absolute values why not display the differences?
Perhaps two charts, one with the absolute as shown and another with the variance.
Answers 4
Multiply the values with Hundred or More And then change the Axis unit to None.
Answers 5
by using the differences between the three tests I got much more variance in values. Basically, I took the difference between:
- Test 1- Test 2
- Test 2 - Test 3
- Test 1 - Test 3
I don't know if this is what you are looking for, but it clearly shows the differences of values between the tests. I hope this helps!
Answers 6
It is not possible to change the axis scale for different angles on a radar chart. However, we can create a scatter plot that recreates a radar chart where you can specify the range of values for each angle, essentially allowing you to specify the axes limits as per your question.
We'll create a scatter series for each test. For each metric, you'll specify the axis range you want. We'll place each score along the axis range you specify, and then point the values at different angles based on the metric, simulating a radar chart. Each series will then loop back to metricA again to close the loop.
Setting up the data:
Angle (360 degrees split up into 5 angles):
- metricA:
0 - metricB:
=2*PI()/5 - metricC:
=2*2*PI()/5 - metricD:
=3*2*PI()/5 - metricE:
=4*2*PI()/5
Scale lower and Scale upper: Freely set the axis range for each metric
x1: (x-coordinate for each scatter point)
In cell F2: =SIN($B2)*(E2-$C2)/($D2-$C2)
y1: (y-coordinate for each scatter point)
In cell G2: =COS($B2)*(E2-$C2)/($D2-$C2)
The formulas for x and y can be filled down for all metrics, and then copied across for each test.
Finally, duplicate metricA in the bottom row: In cell A7: =A2 And fill this across all columns
Then, create a scatter plot with lines between the points:
- Add each series one at a time
- Set the x and y axes to fixed ranges from -1 to 1
- Resize the plot area to be square-shaped
- Hide the major gridlines, tick marks and axis labels to make the scatter plot look more like a radar chart
You manually enter any values you want for scale lower and scale upper values. For my chart, I set the lower limit of each metric as being the lowest value minus 10% of the difference between highest and lowest value, and I set the upper limit as being the highest value plus 10% of the difference between highest and lowest value. This result in all lowest points being the same distance from the middle and all highest points being the same distance from the middle. The middle value for each metric is scaled based on it's value compared to the highest and lowest values.
Scale lower: =MIN(E2,H2,K2)-(MAX(E2,H2,K2)-MIN(E2,H2,K2))/10
Scale upper: =MAX(E2,H2,K2)+(MAX(E2,H2,K2)-MIN(E2,H2,K2))/10
If you needed, it would also be possible to add pseudo axes and gridlines to make the scatter plot look even more like a radar chart. It would just be necessary to add additional series to plot the axes and gridlines and format them as thin gray lines to mimic normal axes and gridlines.
0 comments:
Post a Comment