Forum Discussion

ntlknight's avatar
ntlknight
Copper Contributor
Oct 16, 2023

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...
Charting
Excel
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Oct 16, 2023

    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's avatar
ntlknight
Copper Contributor
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,
Nathan
Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Oct 16, 2023

ntlknight 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.

 

 

  • ntlknight's avatar
    ntlknight
    Copper Contributor
    Oct 16, 2023

    Yes, I do use some conditional formatting with data bars for visually seeing progress. The world of Charts in Excel is a big one and I'm still finding some that inspire more than others and some that work better than others for visually getting a glimpse of what's going on. Until I get the charts on the screen and use them or a month or 2, it's hard to tell which ones are the most useful...and I'm enjoying the gauge/donut style chart as a 2nd or even 3rd visual stimulant to see where the progress is for the day/week/month/year...

    • Riny_van_Eekelen's avatar
      Riny_van_Eekelen
      Platinum Contributor
      Oct 16, 2023

      ntlknight That's clear then. Thanks for the feed-back.

      • ntlknight's avatar
        ntlknight
        Copper Contributor
        Oct 16, 2023

        Riny_van_Eekelen I think I may need your help one more time, if you're able to! I'm very close to having the lower part of the gauge (-0% and +100%) to use 2 different colors. I just don't understand the formulas enough (that you created) to know how to separate the negative from the positive...and assign those 2 parts to 2 different charts sitting on top of each other.

         

        I've duplicated the doughnut chart (by CTRL+C and CTRL+V) and then dragging the duplicate chart right on top of the original one. I then 'made invisible" the parts of the 2nd doughnut (the upper gauge part) that I didn't need to show (since the first instance shows that part already). But then I tried to create a new formula that would only populate this new gauge/chart if it went in the negative...but I couldn't figure out how to do that. Are you able to help me either create a couple more lines of formula to use for the "Select Data" of the 2nd instance of the Doughnut chart?--or have one set of formulas to use in the "Select Data" for the positive and another for the negative charts?

         

        Thank you once again!

         

        Nathan

Resources