So this is a fairly old thread, but I came across it while browsing around about gauge / dial graphs. I made one of the pie chart dials for a MI dashboard, but it doesn't please me (mainly for aesthetic reasons) so I decided to see if I could find a better way in my free time... That link above looks good, and I'll give doughnut charts a go, but before seeing that I was using XY Scatter graphs, which look great and are really flexible. But…
I am having a bit of a problem with the needle on my XY Scatter dial chart, and making it point in the right direction. This may be more of a mathematical question than an Excel one, but any help would be greatly appreciated... (I have listed formulae at the bottom of each paragraph to avoid losing the sense of the description - these are referenced with parenthesised numbers).
You can plot a circle for the edge of the dial using the circle equations(1) Use an XY scatter graph with a smoothed line and control the scale by making a column of theta values, and varying the increment between points on the line(2). delete x and y values from the top and bottom of the list for an incomplete circle. I have used a dynamic range to plot the x and y values without blanks, and so that the circle does not need to be complete(3). So far, so good – I can plot all or part of a circle which looks like a string of evenly spaced beads. Call this the scale series
Code:
1. A circle can be drawn by plotting x against y for a given theta where:
x = a + r*cos(theta) and y = b + r*sin(theta)
The circle is centred on (a,b) with radius r. theta is taken from a list (see 2.).
2. Another column is made to contain a list of theta values where
theta = 2*pi()-(2*pi()/360/increment)*(rows($A$1:A1)-1)
this should be dragged down until theta = 0
For 10 Degrees between points, the increment would be 10, for 10 points in a
complete circle, it would be 36.
3. Dynamic Range Formulae, StartX and StartY are the coordinates of the first
point on the circle, EndX and EndY are those of the the last point. The X and
Y values for the circle mentioned in 1. are listed in columns AB and AC.
StartX:=INDEX(OFFSET(Sheet1!$AB$1,COUNTBLANK(Sheet1!$AB$1:$AB$19),0,COUNTA(Sheet1!$AB$1:$AB$37),1),1)
StartY:=INDEX(OFFSET(Sheet1!$AC$1,COUNTBLANK(Sheet1!$AC$1:$AC$19),0,COUNTA(Sheet1!$AC$1:$AC$37),1),1)
EndX:=INDEX(OFFSET(Sheet1!$AB$1,COUNTBLANK(Sheet1!$AB$1:$AB$19),0,COUNTA(Sheet1!$AB$1:$AB$37),1),COUNT(OFFSET(Sheet1!$AB$1,COUNTBLANK(Sheet1!$AB$1:$AB$19),0,COUNTA(Sheet1!$AB$1:$AB$37),1)))
EndY:=INDEX(OFFSET(Sheet1!$AC$1,COUNTBLANK(Sheet1!$AC$1:$AC$19),0,COUNTA(Sheet1!$AC$1:$AC$37),1),COUNT(OFFSET(Sheet1!$AC$1,COUNTBLANK(Sheet1!$AC$1:$AC$19),0,COUNTA(Sheet1!$AC$1:$AC$37),1)))
To make the needle on the dial, I need to add a second data series to the graph, which will consist of only two points – one will be the pivot of the needle (the same as the centre of the circle), and the other will be the end of the needle. It is plotting the end of the needle consistently which causes the problem.
The basic method of plotting the needle position is to take three numbers – the maximum and minimum of the scale series, and the percentage of the way through this range the value to be plotted is.(4) This percentage will be referred to as VAL%. In order for the needle to point in the right direction, it will need to bisect the angle between the start and finish points of the scale series in proportion to the VAL% .
Code:
4. VAL%=(value-min)/(max-min)
The angle range of the scale series can be calculated by working out the angle to the start and end points of the scale. I calculated all angles increasing clockwise about the origin from a vertical line down from the origin(5). Once the start and end angles for the scale series are worked out, it is easy to work out the angle range of the graph – how far round the scale goes(6).
Code:
5. In principle The ANGLE of the start (StartA) and end (EndA) points from
the vertical below the origin are:
StartA=DEGREES(ATAN(StartX/StartY)) and EndA=DEGREES(ATAN(EndX/EndY))
However because the TAN() function has a period of less than a complete circle,
this will not give correct results for the graph – compensations need to be made:
StartA=ROUND(IF(StartX<=0,IF(StartY<0, DEGREES(ATAN(StartX/StartY)),180- DEGREES(ATAN(StartX/StartY))),IF(StartY>0,180+ DEGREES(ATAN(StartX/StartY)),360- DEGREES(ATAN(StartX/StartY)))),3)
EndA= ROUND(IF(EndX<=0,IF(EndY<0, DEGREES(ATAN(EndX/EndY)),180- DEGREES(ATAN(EndX/EndY))),IF(EndY>0,180+ DEGREES(ATAN(EndX/EndY)),360- DEGREES(ATAN(EndX/EndY)))),3)
6. The angle range (RangeA) of the scale series is the difference between
the start and end angles.
RangeA=(IF(EndA=0,360,EndA)-StartA)
The If() statement is needed because even if the end of the scale series is
at zero, the series will go all the way round to reach this point
(i.e. The graph displays a complete circle).
Now we know how far round the scale goes, we can work out where to put the needle, which will be the VAL% of the way around the graph, starting at the angle of the start of the scale, and ending at the end of the scale. The position can then be calculated from this angle of the needle, given that the length can be inputted.
These (or maybe just one of these) formulae are not working because they are wrong…
The needle angle, An =(-1*Range*VAL%)+StartA”
The x coordinate of the end of the needle, Xn=IF(An<90,-1*(ROUND(Length*(SIN(RADIANS(An))),5)),ROUND(Length*(SIN(RADIANS(An))),5))
The y coordinate of the end of the needle, Yn=IF(An<90,-1*(ROUND(Length*(COS(RADIANS(An))),5)),ROUND(Length*(COS(RADIANS(An))),5))
Any suggestions?
If anyone would like the workbook I am making all this in, I can provide it if you let me know the right place to send it.