Adjusting The Needle of Speedometer Chart

**biglou82** · 10-08-2014, 01:42 PM

Hello All,

I am creating a dashboard that will show someone's quality in a single quick glimpse. I have been able to create the speedometer look and am quite happy with it. With that being said, the needle moves on a percentage base from 0% to 100% accordingly. However, 90% of the quality results are greater than 50%, and so I am trying to show the 180* view but from 50% to 100%. So if the needle is at 9:00 it reflects 50%, 12:00 it reflects 70%, and 3:00 reflects 100%. After hours of trial and error I am unsuccessful. IF this is possible, what adjustments do I need to make? Thanks for your help!!!

**shg** · 10-08-2014, 02:04 PM

If 50% is at 9:00, where is 0%?

**biglou82** · 10-08-2014, 02:24 PM

Well 0% will never be a possibility based on our system. But I guess I am simply looking to limit the scatter plot needle to only reflect 50% to 100% within the 180* frame. So to answer your question 0% would be at 3:00 also and 25% at 6:00. I know I'm no expert, so is what I'm asking make sense?

**shg** · 10-08-2014, 02:41 PM

Row\Col	O	P	Q
6	Value
7	65%		O7: Input
8	x	y
9	0	0
10	-0.588	0.809	O10: =COS(RADIANS(-360*O7))
11			P10: =SIN(RADIANS(-360*O7))

**biglou82** · 10-09-2014, 08:54 AM

SHG,

Thank you for your assistance! I completed a scatter plot with fixed lines based on your formula, however the needle does not turn like it should. Changing the input simply changes the length of the needle fixed on "12:00".

**MrShorty** · 10-08-2014, 02:52 PM

Here's how I'd probably do it:

1) Maybe I am too much of a mathematician, but I would drop all use of angles in degrees and perform all angle calculations in radians. Not angle conversions to become confused with. Along those same lines, I would think about my speedometer in terms of a unit circle (http://en.wikipedia.org/wiki/Unit_circle and/or http://www.purplemath.com/modules/unitcirc.htm if you have forgotten this concept). I find that so many trig problems (like this one) become a lot easier when viewed on the unit circle.

2) It appears that your intention is use linear interpolation to interpolate between the min and max values and the min and max angles. Putting things on the unit circle, this means you want max% at theta=0 and min% at theta=pi and linearly interpolate between. This can easily be done using the TREND() function.

   --  min -- max
values -- 0.5 -- 1
angles -- =pi() -- 0

=TREND(angles,values,needle%)

http://office.microsoft.com/en-us/ex...in=HP010342656

With the angle theta thus calculated, plotting the end of the speedometer needle is as simple as x=cos(theta) and y=sin(theta).

Now I understand Jon Peltier means when he says that one of the reasons he doesn't like speedometer charts is that he spends so much effort explaining the trig behind them.

**shg** · 10-08-2014, 03:02 PM

I would not have 0 to 100% cover the full circle, but like an actual speedometr, have an unused area at the bottom -- maybe +/- 15 degrees on either side of 6:00. And just the doughnut accordingly.

**biglou82** · 10-09-2014, 08:01 AM

Thank you all for your replies. I think changing it to a unit based "needle" would be more appropriate. How would I go about changing it from percentage to unit based?

MrShorty,
Thanks for your detailed explanation of how to fix it. Unfortunately your description is quite a bit above my mathematic equivalency. Would you be able to show an attachment of your explanation? Thanks again

**shg** · 10-09-2014, 05:38 PM

See attached.

**biglou82** · 10-10-2014, 10:22 AM

Woo Hoo! I actually got your chart to work the way I like it! Thank you so much for your help. However for some reason I cannot duplicate it. My needle just changes length only. How did you incorporate the Pct & Angle into the chart in cells L4:M7? I was using a scatter plot with straight line using cells L13:M14. Should I be doing something different?

**biglou82** · 10-10-2014, 08:25 AM

Thanks SHG! However it still does not show the correct angle of the needle when the input is a certain %. For example the arrow should point to 9:00 at 50% and 3:00 at 100%. I played around with the M4 input to see if that made a difference and it does not. Any other suggestions or changes to the "Scratch" sheet that may work. Now I just feel it's an angle issue.

Adjusting The Needle of Speedometer Chart

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Adjusting The Needle of Speedometer Chart

Re: Adjusting The Needle of Speedometer Chart

Re: Adjusting The Needle of Speedometer Chart

Re: Adjusting The Needle of Speedometer Chart

Re: Adjusting The Needle of Speedometer Chart

Re: Adjusting The Needle of Speedometer Chart

Re: Adjusting The Needle of Speedometer Chart

Re: Adjusting The Needle of Speedometer Chart

Re: Adjusting The Needle of Speedometer Chart

Re: Adjusting The Needle of Speedometer Chart

Re: Adjusting The Needle of Speedometer Chart

Thread Information

Users Browsing this Thread

Similar Threads

Speedometer Chart (Problem with the needle/pointer)

[SOLVED] Speedometer needle getting hide

Needle position on Speedometer Graph that uses 300 degrees vice 180 degrees.

Speedometer chart 90-100

Speedometer chart

Bookmarks

Bookmarks

Posting Permissions