Forum Discussion
How To Create a Gauge-Style Doughnut Chart that Includes Negative Numbers & Greater than 100% Values
Hello, I need to create a gauge-style doughnut Chart that not only shows the 0% - 100% range, but that will also show ranges that fall below 0%, or that go over 100%.
By following tutorials on YT, I've made gauge-style doughnut charts that look like this...
...and different charts that combine 2 doughnuts to look like this... (combining 2 tutorials)
As much as I like the chart above with the 100 slices, the solid-fill chart works best for the spreadsheet I'm working on since I have to reduce the size down to very small (and the slices don't show up very clear when zoomed down so small), since I need to visually see up to 23 charts (one for each day of the month) on a single sheet, like this...
These doughnut charts are rotated to a 270-degree angle and the 0% - 100% values work fine, but when given a value of minus 0%, the gauge doesn't work properly (rather than the gauge going the opposite direction, it fills part of the gauge...in the same direction as it would when a positive number is given. And when a value of greater than 100% is given, it doesn't go beyond the 100% level.
In order to visually describe what I'm trying to achieve, I've created in Photoshop what I need the chart to do, I hope that's OK.
Here is a picture of the chart (no Photoshop) that works perfectly fine when no value less than 0% or greater than 100% is given. (The inner (purple) doughnut represents the "daily target" which is always going to occupy the 0%-100% range, since that never changes. The outer (blue) doughnut represents the "actual" figure...and this figure will very often be either a negative value, or a value greater than 100%)...
Here is a Photoshop'ed picture...showcasing what I would like the outer doughnut (the "actual" value) to look like when a negative value is given to it...
And here is another Photoshop'ed picture...showcasing what I would like the outer doughnut (the "actual" value) to look like when a value greater than 100% is given to it...
Thank you so much for your time! I would appreciate any help in being able to create this gauge chart that allows for negative values and values over 100%.
Kind Regards,
Nathan
ntlknight Not exactly what you had in mind with regard to the vivid colours, but close. See attached.
The driving force sits out of view below the chart.
12 Replies
ntlknight Not exactly what you had in mind with regard to the vivid colours, but close. See attached.
The driving force sits out of view below the chart.
Wow, I don't know how to thank you! Thank you so much...this is very nice! I appreciate that you took the time to help me figure this out...it looks really, really good!
I hope you won't mind if I ask a few further questions.
First of all, I ported your chart/formulas over to my spreadsheet and did some color adjustments...and here's how it looks...
I added a thin black border to the inner doughnut/gauge to add some separation between the 2 doughnuts. Is there a way to add some space between the 2 doughnut instances and let the (gradient) background color be the separator instead of adding the border?
If I enter a negative value to the "Actual" field that exceeds the "Target" field, it doesn't populate the negative portion of the gauge...it disappears. As long as the "Actual" field is negative by an amount that is equal to the "Target" amount (in the case here, if the "Target" is 3,000 and the negative value is between -1 and -3,000 (shown below), the lower part of the doughnut/gauge populates perfectly, but if the negative value goes below -3,000, (more than -100% also shown below), it disappears.
Conversely, if the "Actual" value exceeds the "Target" by more than twice the "Target" amount (more than 200%), it throws off the axis of both the upper and lower parts of the outer ("Actual") doughnut.
Would it be possible to "cap-off" the negative part of the doughnut (the lower/outer part) at -100%...so that any value over -100% will be the same as if it were -100%? And conversely, could the positive part of the doughnut (the part that exceeds 100%) also "cap-off" at +200%, so that any value over 200% will be treated the same as if it were 200%?
Last thing I wanted to ask is if there is any chance we could have the negative part of the "Actual" doughnut be a different color than the positive part that exceeds 100%---so that a different color can be used for the negative, like reds, while the positive side could use greens.
Again, I can't thank you enough for this wonderful chart you've helped me figure out!
Best Regards,
Nathan
ntlknight To be honest, I'm not a big fan of pie- or donut charts and have no real hands-on experience about all the formatting features for background, donut borders and sizes.
With respect to the limitation of plus or minus 100%, I was aware of that but didn't know how likely it would be if an actual would be more than 100% off target. Referring to my original file, change the formula in D5 to:
=IF(C5<0,MAX(-1,C5/C7),MIN(2,C5/C7))That will set limits the negative and positive scores to not less then -100% and not greater than 200% respectively. Then you'll probably want to change the settings of the data labels to indicate that a capping has been applied.
Coloring negative performance red and positive over-performance green would not work in this setup as both values come from the same slice. And a slice can only have one color, unless you manipulate it with VBA, based on the situation. And that is something I do not get involved in. Sorry.
