speedometer/gauge Chart in excel 2007

chickadeejen

Board Regular
Joined
Dec 15, 2004
Messages
186
Morning,

I'm using excel 2007 and I'm looking at creating a speedometer/gauge Chart. How do you do this in excel 2007?

Thank you,
Jen
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Yard

Well-known Member
Joined
Nov 5, 2008
Messages
1,929
Have a look at this for a Carlos Fandango version:

http://www.brainbell.com/tutorials/ms-office/excel/Create_A_Speedometer_Chart.htm

Or you can just create a pie chart, add a series to your data which is set to 50% of the total values, then divide your existing values by 2.

This creates a pie chart with 1 data point taking up half the chart, and your "actual" data points making up the other half. Make the "dummy" half invisible, rotate the pie and there you have it.
 

thomas75

New Member
Joined
May 13, 2010
Messages
3
BEGRAPHIC.COM gives you FOR FREE 3 types of innovative graphics inside Excel and PowerPoint (from version 2000 to 2010 64bit) :

1 new types of charts for showing performance, known as gauges and meters

2 thematic maps, also called choropleth maps
(you can even receive 10 000 maps for free if you send this request using your business email)

3 the largest collection of sparklines / in-cell graphics
(No other software has such a range of micro-charts)
 

JustHangin

New Member
Joined
Aug 3, 2010
Messages
1

ADVERTISEMENT

Beware: BeGraphic.com does have a free download. I tested it and it leaves much to be desired.

It has a nice looking gauge but the setup is absolutely frustrating. It forces you to pick a cell even though its asking to choose a color - huh? It makes no sense. There are so many nuances that I uninstalled it. Don't waste your time with it. I'm just being honest.
 

thomas75

New Member
Joined
May 13, 2010
Messages
3
The beauty of BeGraphic is that you can pilot everything (even the color) just from cells.

So when you will use color-coding with "conditional formatting" (very useful Excel function), you won't have to change the color of the gauge. BeGraphic will do it for you.

Use Excel conditional formatting, then you will understand BeGraphic's way of working : dynamic cell-driven graphics.

Think cell ! Excel is so smart.
 
Last edited:

xld

Banned
Joined
Feb 8, 2003
Messages
5,378

ADVERTISEMENT

My advice, dont use a guage chart. They are glitzy and uninformative, there is invariably a better alternative.
 

thomas75

New Member
Joined
May 13, 2010
Messages
3
Depending on what you are doing, gauges might be useful/ inefficient.

Gauges give much more than one value. It gives the minimum, the maximum, the current value and how far from the maximum you are... in milliseconds, without deciphering figures.

That is why aircraft pilots heavily use them (Boeing and Airbus have made deep research, and still their "glass ****pit" are mainly made of circular and half-circular gauges).

Doctors also use gauges, but straight ones (called thermometers)... for obvious bodily reasons.

Thermometers are called "Bullet charts" by Stephen Few (adding target / color-coding).
 
Last edited:

High Plains Grifter

Board Regular
Joined
Mar 9, 2010
Messages
129
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.
 

ST_JENKINS

New Member
Joined
Jun 13, 2011
Messages
1
The easiest way to plot a line is two points, so in your XY scatter graph, add a second series with two points, namely your pivot (which you have already calculated (a,b).

The value of the end point on the circle is calculated by:

y=sqrt(r^2-(x-a)^2)-b

Since you also know the originating value that you want to plot (x), you can now get y.

I have an example of this guage plot if you are interested...
 

Watch MrExcel Video

Forum statistics

Threads
1,129,751
Messages
5,638,154
Members
417,010
Latest member
jnuss03

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top