Speedometer-type chart with negative values

Getting this to work for negative numbers basically means "translating" the numeric data so the chart doesn't see negative data. Here's what I did:

1) In order to get data/category labels in the donut chart part, I need each of the data/category labels in the spreadsheet. You haven't specified exactly what "division" value you want, so I pick 25. In A21:A29, I enter -100,-75,-50... In A30, I enter some place holder value (I used "offscale")
2) Each division needs an appropriate "area" value for the chart to use to make an appropriate size donut block. Since I am using 25 unit divisions, I enter 25 into B21:B24 and B26:B29. In B25 I enter 0 and in B30, I enter 200.
3) Now, let's put our new donut data into the donut chart. Select the chart -> Select Source Data -> Select the "meter" series -> Edit -> change the "values" range so it is using B21:B30. With the "meter" series still selected -> Edit category axis data -> use A21:A30. Confirm choices to exit Select Data dialog. You should now have a donut chart with eight segments in the upper half and one large segment across the bottom half.
4) At this point, I notice that the donut chart "begins" (as Excel sees it) at the west (270 degrees) position, but the pie chart pointer begins at the north (0 degrees) position. In order to get the pie and donut chart to begin in the same place, I format the pie chart "pointer" series so that it begins in the same position as the donut chart -- at 270 degrees.
5) I also notice that the pie chart "pointer" is assuming 200 units to make a full circle (the 200- in the E5 formula), but the donut chart is using a 400 unit full circle. I want my pie chart and donut chart to have the same "size" circle, so I edit the formula in E5 to be =400-....
6) Now the pie pointer series counts from 0 to 400 around the circle, but I really want that to mean -100 to 300, so I change the formula in E2 to =reading+100 (if I put my reading in F2, then this is =F2+100). What the chart will see in E2 is 0 to 200 (since I assume values above 200 are "offscale" and are invalid). What I will see in F2 is -100 to 100.

At this point, the speedometer chart could be complete and should work. A couple of formatting steps to clean it up:
7) In order to see the data labels, select the donut chart "meter" series -> add data labels -> show category name. I don't have any easy automatic options for moving these data labels, but I can select and drag each label to where I want it.
8) Instead of using different colors for each 25 unit segment, you want everything meaning "less than 0" to be red and everything meaning "above 0" to be green, so I can select each data point in the donut "meter" series and format it to be the appropriate color. If you don't want to see the "offscale" section/data point, simply format it to have no color. You can delete the "offscale" value in A30 to get rid of the "offscale" data label.

That should be fairly complete (unless there are other minor formats you want to change). Whatever value you enter in F2 (=randbetween(-100,100) in F2 would make a reasonable test) should show up on the speedometer chart correctly.

Will that work? Where do you get stuck?

Originally Posted by MrShorty

Getting this to work for negative numbers basically means "translating" the numeric data so the chart doesn't see negative data. Here's what I did:

6) Now the pie pointer series counts from 0 to 400 around the circle, but I really want that to mean -100 to 300, so I change the formula in E2 to =reading+100 (if I put my reading in F2, then this is =F2+100). What the chart will see in E2 is 0 to 200 (since I assume values above 200 are "offscale" and are invalid). What I will see in F2 is -100 to 100.

Thank you for a really detailed reply I was almost able to follow it to the end, but I lost track at step 6. I attached what I managed to make, could you kindly help a bit more?

E: Got it! Didn't realize to change pointer 'End'-value to 300-... at first, now it works like a charm. Thank you!