Excel Speedometer/Gauge

[rniculae]

Hello guys,

I have searched the forum but i cannot find something similar to my actual problem.
I tried to make a classic speedometer with doughnut and pie chart and it does not work properly (ex. my value is 80 and the needle is somewhere else totally)

I use excel 2013.

The speedometer should only be between those values
I will list my values below as well my thresholds for the chart.

[TABLE="class: grid, width: 100, align: left"]
<tbody>[TR]
[TD]C/R[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Values[/TD]
[TD]Red[/TD]
[TD]Light red[/TD]
[TD]Yellow[/TD]
[TD]Light Yellow[/TD]
[TD]Green[/TD]
[TD]Light Green[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][TABLE="width: 86"]
<tbody>[TR="class: grid"]
[TD="width: 86, align: right"]53%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]30%[/TD]
[TD]40%[/TD]
[TD]50%[/TD]
[TD]60%[/TD]
[TD]70%[/TD]
[TD]80%[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][TABLE="width: 86"]
<tbody>[TR="class: grid"]
[TD="width: 86, align: right"]87%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]76%
[/TD]
[TD]80%
[/TD]
[TD]84%


[/TD]
[TD]88%


[/TD]
[TD]92%


[/TD]
[TD]96%


[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][TABLE="width: 86"]
<tbody>[TR="class: grid"]
[TD="width: 86, align: right"]36%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]50%


[/TD]
[TD]40%


[/TD]
[TD]30%


[/TD]
[TD]20%


[/TD]
[TD]15%


[/TD]
[TD]10%


[/TD]
[/TR]
</tbody>[/TABLE]














Can you guys please help me out? I am stressed out for the last 3 weeks. Tried everything and i cannot figure it out.

Thank you very much,
Razvan

 

[rniculae]

Hey,

I really need an answer. I have to finish something with this speedometers and present it to the board.

Please, any opinion about this really helps me out.

Thank you,
Razvan

 

[hiker95]

rniculae,

In the MrExcel opening/banner page (top right), click on Advanced Search

In the Keyword(s): box

Enter the following with the " characters:

"Speedometer Gauge"

And, click on Search Now

 

[rniculae]

Hiker, i have already searched this forum, no post will actually solve my issue because i have 6 ranges and i don't need it from 0-100. If i try to modify it, it will not work anymore and my value will be shown somewhere else than where it supposed to be. It's not scaling correctly.
Chandoo's tutorial works for 3 areas only and with values summed to 100.

Thank you

 

[hiker95]

Hiker, i have already searched this forum, no post will actually solve my issue because i have 6 ranges and i don't need it from 0-100. If i try to modify it, it will not work anymore and my value will be shown somewhere else than where it supposed to be. It's not scaling correctly.
Chandoo's tutorial works for 3 areas only and with values summed to 100.

Thank you

rniculae,

You are welcome.

Sorry that I was not able to help.

 

[rniculae]

no problem, maybe someone else could help me out

thanks
razvan

 

[DRSteele]

Did you try searching the internets for speedo charts that have many colours? See here: Excel Professor: Speedometer Chart / Gas Gauge Chart

 

[DRSteele]

I think maybe your problem is arising because you haven't converted your percentages to portions of 180 degrees. Using the data you provided and the guidance from the link I provided, this should be your dataset.

	A	B	C	D	E

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFC000"]Percentage[/TD]
[TD="bgcolor: #FFC000"]Degrees[/TD]
[TD="bgcolor: #FFC000"]Dial Colours[/TD]
[TD="bgcolor: #FFC000"]needle[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]180[/TD]
[TD="align: right"]180[/TD]
[TD="bgcolor: #E2EFDA"]100% share[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]30%[/TD]
[TD="bgcolor: #E2EFDA, align: right"]54[/TD]
[TD="bgcolor: #E2EFDA, align: right"]54[/TD]
[TD="bgcolor: #E2EFDA, align: right"]179[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]40%[/TD]
[TD="bgcolor: #E2EFDA, align: right"]72[/TD]
[TD="bgcolor: #E2EFDA, align: right"]18[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]50%[/TD]
[TD="bgcolor: #E2EFDA, align: right"]90[/TD]
[TD="bgcolor: #E2EFDA, align: right"]18[/TD]
[TD="bgcolor: #E2EFDA, align: right"]-2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]60%[/TD]
[TD="bgcolor: #E2EFDA, align: right"]108[/TD]
[TD="bgcolor: #E2EFDA, align: right"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]70%[/TD]
[TD="bgcolor: #E2EFDA, align: right"]126[/TD]
[TD="bgcolor: #E2EFDA, align: right"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]80%[/TD]
[TD="bgcolor: #E2EFDA, align: right"]144[/TD]
[TD="bgcolor: #E2EFDA, align: right"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]100%[/TD]
[TD="bgcolor: #E2EFDA, align: right"]180[/TD]
[TD="bgcolor: #E2EFDA, align: right"]36[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA, align: right"]360[/TD]
[TD="bgcolor: #E2EFDA, align: right"]360[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet7​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E2[/TH]
[TD="align: left"]=E3&"% share"[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B3[/TH]
[TD="align: left"]=C3+B2[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C3[/TH]
[TD="align: left"]=(A3-A2)*$C$2[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D3[/TH]
[TD="align: left"]=D2/100*E3-1[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D5[/TH]
[TD="align: left"]=360-SUM(D2:D4)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B4[/TH]
[TD="align: left"]=C4+B3[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C4[/TH]
[TD="align: left"]=(A4-A3)*$C$2[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B5[/TH]
[TD="align: left"]=C5+B4[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C5[/TH]
[TD="align: left"]=(A5-A4)*$C$2[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B6[/TH]
[TD="align: left"]=C6+B5[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C6[/TH]
[TD="align: left"]=(A6-A5)*$C$2[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B7[/TH]
[TD="align: left"]=C7+B6[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C7[/TH]
[TD="align: left"]=(A7-A6)*$C$2[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B8[/TH]
[TD="align: left"]=C8+B7[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C8[/TH]
[TD="align: left"]=(A8-A7)*$C$2[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B9[/TH]
[TD="align: left"]=C9+B8[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C9[/TH]
[TD="align: left"]=(A9-A8)*$C$2[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C11[/TH]
[TD="align: left"]=SUM(C2:C9)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D11[/TH]
[TD="align: left"]=SUM(D2:D9)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[rniculae]

GENIUS. I knew i was doing something wrong on linking my values to degrees..

Thank you bro, you helped me a lot!
Have a great day!

 

[DRSteele]

You're welcome. I'm glad it worked out for you.