PDA

View Full Version : Spedometer Type Chart (Donut) - Help with Formula



rconverse
08-03-2010, 09:28 AM
This is a repost from another board where I didn't have much luck. Hopefully I'll do better over here.

http://www.mrexcel.com/forum/showthread.php?p=2397221#post2397221


I'm trying to duplicate a chart from this tutorial.

http://www.datapigtechnologies.com/f...augechart.html (http://www.datapigtechnologies.com/flashfiles/gaugechart.html)

I've got everything working, expcept I don't want the chart to start at 0% and end at 100%. I want the lowest part of the chart to be 90%. I can't get the pointer to work correctly. Here are the formulas.

E1 is the fill rate. So in this example, let's say that value is 93%.
H10 is the highest value on my chart. That's 100%.

H1 is what ends moving the slice or "pointer".
=MOD(270+180*E1/H10,360)

Around the "spedometer", I have five labels. However, instead of having (going clockwise) 0, 25%, 50%, 75%, 100%, I have 90%, 92.5%, 95%, 97.5%, 100%. The result of the equation above tells VBA (code below) where to move the slice to represent the correct value. Again though, it's not corresponding correctly with the labels I'm using.

Does anyone know a way I can change the formula to be more dynamic with the labels?


Sub UpdateChart()
Application.ScreenUpdating = False
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.ChartGroups(1).FirstSliceAngle = ActiveSheet.Range("H1")
ActiveSheet.ChartObjects("Chart 6").Activate
ActiveChart.ChartGroups(1).FirstSliceAngle = ActiveSheet.Range("H1")
Worksheets("sheet1").Activate
Range("A1").Select
Application.ScreenUpdating = True
End Sub

I've attached the spreadsheet for reference. I have two charts setup so hopefully it will be easy to see what I'm talking about.

Thanks,
Roger

rconverse
08-16-2010, 02:47 PM
To the top.