Gauge Chart - Getting Needle to Work

[Malikai]

Howdy folks,

My first post here, so thanks in advance and bear with me. Our manufacturing plant is audited monthly and given a score. Based on the score, associates are given a bonus of a percentage of earnings during the month. I'm trying to create a guage (speedometer) chart that will show both the audit score and the associated bonus payout percentage.

Any score below 75 pays out zero, and any score 80 or higher pays out 1.25%.

I would like the user to be able to enter the audit score and have the chart needle reflect the score and line up with the bonus percentage as well.

With a range of 74 to 80, I calculated the slices at 0.6. I am able to create two doughnut charts and change one of them to display the payout values, but I cannot figure out how to get the needle to move properly and line up with the audit score.

For example, when I enter 75 for the audit score, I get the first image. If I change it to 79, rather than moving to the right side, I get this 2nd image.

I am open to including a higher range of values, but I do not expect to ever see scores below 50 or higher than 95. If it helps create the chart, I am open to it.

Thanks a bunch!

 

[sxhall]

It is to do with the scale in which the needle moves. you are only moving 1 to 6points i(74 to 80) in relation to the segments. Each segment accounts for a 14th of the graph range(!) meaning to move the needle it needs to ncrement 534/14-5 (needle thickness) each time. Really hope this makes sense...!

I've assumed your needle is on a seperate chart in which case I knocked this up quickly and added in another range to look up the needle position...

I added in the below formulas...

Column Y, NEW Position...
Values represent the movement for the needle for each increment of 1 = Sum(529/14)*X where X is the increment up from74 so 1,2,3,4,5 or 6. I have rounded the figures in the below as well.

Column AB, Position - This one looks up the score column W for me and returns the needle increment to move by, column Y for me...

=XLOOKUP(AB7,$W$8:$W$15,Y8:Y15)

Column AB, Rest - this is the total range of the graph 529 (already less the needle thickness) minus the position

=529-AB8

Audit result - is no longer in the range for the graph, this sits outside of it and is used to lookup the 'Position' value.

I also adjusted the 'Position of first slice' to be 270...

My results returned the below, 80...

77...

70...

If you were to add 9 to each of the posistion it would come to tne centre of each segment...

Really hope this makes sense I've read it several times and added in more explanations each time!

Steven

 

[Malikai]

It is to do with the scale in which the needle moves. you are only moving 1 to 6points i(74 to 80) in relation to the segments. Each segment accounts for a 14th of the graph range(!) meaning to move the needle it needs to ncrement 534/14-5 (needle thickness) each time. Really hope this makes sense...!

I've assumed your needle is on a seperate chart in which case I knocked this up quickly and added in another range to look up the needle position...

I added in the below formulas...

Column Y, NEW Position...
Values represent the movement for the needle for each increment of 1 = Sum(529/14)*X where X is the increment up from74 so 1,2,3,4,5 or 6. I have rounded the figures in the below as well.

Column AB, Position - This one looks up the score column W for me and returns the needle increment to move by, column Y for me...

=XLOOKUP(AB7,$W$8:$W$15,Y8:Y15)

Column AB, Rest - this is the total range of the graph 529 (already less the needle thickness) minus the position

=529-AB8

Audit result - is no longer in the range for the graph, this sits outside of it and is used to lookup the 'Position' value.

I also adjusted the 'Position of first slice' to be 270...

View attachment 102721

My results returned the below, 80...
View attachment 102723

77...
View attachment 102724
70...
View attachment 102722


If you were to add 9 to each of the posistion it would come to tne centre of each segment...
View attachment 102725


Really hope this makes sense I've read it several times and added in more explanations each time!

S

 

[Malikai]

Thanks a bunch, Steven! I have a much better understanding of how the pie chart is orienting itself with the two doughnut graphs. Our score was a 79. With your assistance and some formatting, here is the update: