Forum Discussion
How To Create a Gauge-Style Doughnut Chart that Includes Negative Numbers & Greater than 100% Values
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.
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.
- Thank you again for the speedy reply, it is much appreciated. Your new formula worked perfectly...now it is compensating for larger than 200% values and negative values that go below -100% (and unfortunately, in trading there are days where the losses can be more than what the profit target is (meaning more than -100% of what the profit target is)...and the wins will often be more than 2-3 times the daily target...on good days).
Not a problem regarding the red color for the negative and green color for the positive slices. Maybe I'll try to figure out how to copy/paste another donut instance on top of this one so that there can be separate slices that can have alternate color editing when applied and set to invisible when not applied.
Best Regards,
Nathanntlknight Why not use conditional formatting with data bars? That takes up a lot less space and doesn't require all these helper formulas. Something like in the picture below. Up to you of course.
