A technique, not a question.
I recently needed create a radar graph in which the circle was divided into sectors and I wanted the values to be graphed in the centre of each sector, not on the vertices as per normal. Excel doesn't let you rotate a radar chart so I had to make it happen.
The way in which I did this was to create twice as many data points with the actual data in every second position (rows 2, 4, 6 etc.). In every other position, I put the formula as follows. I had my data in a table to make it easier to calculate the top and end of the table.
I also had to create helper columns X, a & b: Score, X, a, b
The formula in column-X is:
= LET(PrevVal, IF(ROW()=MIN(ROW([Score])), OFFSET([@[Score]],ROWS([Score])-1,0), OFFSET([@[Score]],-1,0)),
NextVal, OFFSET([@[Score]], 1, 0),
SQRT(NextVal^2+PrevVal^2-(2*NextVal*PrevVal*COS(RADIANS(360/ROWS([CAP Average])*2))))
)
The formula in column-a is:
= LET(PrevVal, IF(ROW()=MIN(ROW([Score])), OFFSET([@[Score]],ROWS([Score])-1,0), OFFSET([@[Score]],-1,0)),
NextVal, OFFSET([@[Score]], 1, 0),
IFERROR(ACOS(([@X]^2+PrevVal^2-NextVal^2)/2/[@X]/PrevVal),"")
)
The formula in column-b is:
=180-DEGREES([@a])-360/ROWS([Score])
Finally the formula in every alternate (starting at the first row) Score cell is:
= LET(PrevVal, IF(ROW()=MIN(ROW([Score])), OFFSET([@[Score]],ROWS([Score])-1,0), OFFSET([@[Score]],-1,0)),
PrevVal*SIN([@a])/SIN(RADIANS([@b]))
)
Note 1: The above formula goes in rows 1, 3, 5, etc. but the last row of your table must contain the last of you actual data.
Note: It is important that the Score column contains your actual data starting in the second row and every second (4, 6, 8, etc) thereafter and the last row of your Score column contains the last actual data point
Last of all create a radar graph, with markers of the Score column. Then to hide the "phantom" intermediate, calculated scores: Select the markers on your graph, select the first marker individually (the one at the top, vertical vertex), format it and switch the marker off and repeat this process for every second marker. This will leave only the connecting line between your actual data points.
How does it work? It works by using trigonometry to calculate the length of the intermediate values between each actual values so that the lines between your actual values appear straight.
The resulting graph should have the appearance of graphing your actual scores but rotated such that they appear in the centre of the sectors (number of sectors = number of actual scores) something like:
The above graph in fact expands the idea by having major and minor sectors. The minor sectors are the scores and the major sectors are simply fixed values of the maximum, 5 in this example, in this case representing major categories.
The above graph actually has two scores graphed using the same technique
As a further comment on the above graph, the annulus around the graph is created by graphing an additional set of scores = 6, one more than the maximum of the actual scores. The colouring of individual major sectors is produced by creating n x separate fake scores, where n is the number of major sectors, with 6's in the portion to be coloured and 0's elsewhere.
E.g. (Assuming 4 equal sized sectors):
66666000000000000000
00000666660000000000
00000000006666600000
00000000000000066666
You can then format each of these ranges as filled radar graphs and choose your colours.
I hope this makes sense and its of some help. Unfortunately, the only example I have currently contains actual sensitive data, but I will try to find time to create a standalone example workbook at some stage to upload.
I recently needed create a radar graph in which the circle was divided into sectors and I wanted the values to be graphed in the centre of each sector, not on the vertices as per normal. Excel doesn't let you rotate a radar chart so I had to make it happen.
The way in which I did this was to create twice as many data points with the actual data in every second position (rows 2, 4, 6 etc.). In every other position, I put the formula as follows. I had my data in a table to make it easier to calculate the top and end of the table.
I also had to create helper columns X, a & b: Score, X, a, b
The formula in column-X is:
= LET(PrevVal, IF(ROW()=MIN(ROW([Score])), OFFSET([@[Score]],ROWS([Score])-1,0), OFFSET([@[Score]],-1,0)),
NextVal, OFFSET([@[Score]], 1, 0),
SQRT(NextVal^2+PrevVal^2-(2*NextVal*PrevVal*COS(RADIANS(360/ROWS([CAP Average])*2))))
)
The formula in column-a is:
= LET(PrevVal, IF(ROW()=MIN(ROW([Score])), OFFSET([@[Score]],ROWS([Score])-1,0), OFFSET([@[Score]],-1,0)),
NextVal, OFFSET([@[Score]], 1, 0),
IFERROR(ACOS(([@X]^2+PrevVal^2-NextVal^2)/2/[@X]/PrevVal),"")
)
The formula in column-b is:
=180-DEGREES([@a])-360/ROWS([Score])
Finally the formula in every alternate (starting at the first row) Score cell is:
= LET(PrevVal, IF(ROW()=MIN(ROW([Score])), OFFSET([@[Score]],ROWS([Score])-1,0), OFFSET([@[Score]],-1,0)),
PrevVal*SIN([@a])/SIN(RADIANS([@b]))
)
Note 1: The above formula goes in rows 1, 3, 5, etc. but the last row of your table must contain the last of you actual data.
Note: It is important that the Score column contains your actual data starting in the second row and every second (4, 6, 8, etc) thereafter and the last row of your Score column contains the last actual data point
Last of all create a radar graph, with markers of the Score column. Then to hide the "phantom" intermediate, calculated scores: Select the markers on your graph, select the first marker individually (the one at the top, vertical vertex), format it and switch the marker off and repeat this process for every second marker. This will leave only the connecting line between your actual data points.
How does it work? It works by using trigonometry to calculate the length of the intermediate values between each actual values so that the lines between your actual values appear straight.
The resulting graph should have the appearance of graphing your actual scores but rotated such that they appear in the centre of the sectors (number of sectors = number of actual scores) something like:
The above graph in fact expands the idea by having major and minor sectors. The minor sectors are the scores and the major sectors are simply fixed values of the maximum, 5 in this example, in this case representing major categories.
The above graph actually has two scores graphed using the same technique
As a further comment on the above graph, the annulus around the graph is created by graphing an additional set of scores = 6, one more than the maximum of the actual scores. The colouring of individual major sectors is produced by creating n x separate fake scores, where n is the number of major sectors, with 6's in the portion to be coloured and 0's elsewhere.
E.g. (Assuming 4 equal sized sectors):
66666000000000000000
00000666660000000000
00000000006666600000
00000000000000066666
You can then format each of these ranges as filled radar graphs and choose your colours.
I hope this makes sense and its of some help. Unfortunately, the only example I have currently contains actual sensitive data, but I will try to find time to create a standalone example workbook at some stage to upload.
. Just images. I've created an example workbook, and happy to share that if you know how I can do that in this forum.
