excel radar chart - change axes limit values


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.

enter image description here


Answers:


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:

enter image description here

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's STANDARDIZE function.