Speedometer/Gauge Help

[beloshi]

Hi Excel Gurus,

Once again thanks to this platform that I've been learning alot with my VBA macro skills, helping me a lot to shorten my spending my time on one task. I need the suggestion of one speedometer/gauge chart which I am creating I want to add labels to it for example , 0, 10, 20, 30, 40, ------100 around it.

Let me explain some important points for explanation,

I am using combo chart to create Gauge (Donut + Pie Chart) but I am able to put the label on the pointer. But unfortunately I tried to put label on the gauge as explained above.

Please see images for better understanding and as it is mix of two charts and area is already defined in it the lables will work according to the perimeters is it possible to define the labels manually.

ORIGINAL IMAGE

https://imgur.com/hBXvhqG


DESIRED RESULTS

https://imgur.com/CmScvhX


Please your valuable suggestion in achieving this will be highly appreciable.

Added requirement (This is the additional requirement if possible to achieve)

If it can be achieved with vba macro I would also like to know. to create the chart automatically.

 

[VBA Geek]

You can add an XY series to the chart with the following coordinates:

Book1
	I	J	K	L
2			X	Y
3	20%	2.513274	-0.809016994	0.587785
4	50%	1.570796	6.12574E-17	1
5	80%	0.628319	0.809016994	0.587785
6	100%	0	1	0
Sheet2

Cell Formulas
Range	Formula
J3	=RADIANS(180-I3*180)
J4	=RADIANS(180-I4*180)
J5	=RADIANS(180-I5*180)
J6	=RADIANS(180-I6*180)
K3	=COS(J3)
K4	=COS(J4)
K5	=COS(J5)
K6	=COS(J6)
L3	=SIN(J3)
L4	=SIN(J4)
L5	=SIN(J5)
L6	=SIN(J6)

then set the minimum and maximum of both axis to -1 and 1 respectively and hide gridlines and axis labels and use the Add Values From cells feature to add the labels

On a side note, I think bullet charts are a better alternative for your problem: learn how to create one

 

[beloshi]

You can add an XY series to the chart with the following coordinates:

	I	J	K	L

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]Y[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]20%[/TD]
[TD="align: right"]2.513274[/TD]
[TD="align: right"]-0.809016994[/TD]
[TD="align: right"]0.587785[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]50%[/TD]
[TD="align: right"]1.570796[/TD]
[TD="align: right"]6.12574E-17[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]80%[/TD]
[TD="align: right"]0.628319[/TD]
[TD="align: right"]0.809016994[/TD]
[TD="align: right"]0.587785[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J3[/TH]
[TD="align: left"]=RADIANS(180-I3*180)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]K3[/TH]
[TD="align: left"]=COS(J3)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]L3[/TH]
[TD="align: left"]=SIN(J3)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J4[/TH]
[TD="align: left"]=RADIANS(180-I4*180)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]K4[/TH]
[TD="align: left"]=COS(J4)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]L4[/TH]
[TD="align: left"]=SIN(J4)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J5[/TH]
[TD="align: left"]=RADIANS(180-I5*180)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]K5[/TH]
[TD="align: left"]=COS(J5)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]L5[/TH]
[TD="align: left"]=SIN(J5)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J6[/TH]
[TD="align: left"]=RADIANS(180-I6*180)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]K6[/TH]
[TD="align: left"]=COS(J6)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]L6[/TH]
[TD="align: left"]=SIN(J6)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



then set the minimum and maximum of both axis to -1 and 1 respectively and hide gridlines and axis labels and use the Add Values From cells feature to add the labels

On a side note, I think bullet charts are a better alternative for your problem: learn how to create one

Thanks for taking out time to reply, I am confused as to how do I plot these can you tell me which Chart should I select for it now i.e. Scatter etc. Please excuse my nuisance.

With Best regards

beloshi

 

[VBA Geek]

Thanks for taking out time to reply, I am confused as to how do I plot these can you tell me which Chart should I select for it now i.e. Scatter etc. Please excuse my nuisance.

With Best regards

beloshi

To the chart you have now you need to add an XY series with the coordinates I showed above. At the end your chart will be a combo chart with a pie, doughnut and a XY scatter

 

[beloshi]

To the chart you have now you need to add an XY series with the coordinates I showed above. At the end your chart will be a combo chart with a pie, doughnut and a XY scatter

Thank you very much for the help. It worked like a charm