Conditional Formatting in same column with multi criteria

Kelly Gabel

So I want it to be red if the number is less than 0, green if it is greater than 0, and no background if it is blank.

Paul Newcome

It would look something like this...

=IF([YE 2019]@row = "", "", IF([YE 2019]@row < 0, "Red", "Green"))

If it is blank, blank.

If it is less than zero, Red.

If it is anything other than the previous two, Green.

Kelly Gabel

@Paul Newcome You are the best! Thank you!

Paul Newcome

Happy to help! 👍️

Kelly Gabel

@Paul Newcome This questions is off topic for this thread but it is part of the same project I am tasked with. My team would like me to add dashboard widgets that work like a gauge chart but, of course, Smartsheet doesn't have gauge charts. Have you come across any way to mimic that using the options that Smartsheet offers by chance?

Paul Newcome

I haven't really looked into it, but I do have an idea or two. Let me do a little testing to see if I can make something at least sort of work.

Paul Newcome

Can you provide a mocked up example of how exactly you want it to work? Obviously not a Smartsheet example, but something I can use to work towards?

Kelly Gabel

@Paul Newcome Totally. Let me do a mock up to share while you test your ideas. Thanks!

Kelly Gabel

@Paul Newcome UPDATE: I tried to do a mock up of this in Excel and apparently gauge-style chart isn't standard there either, which surprises me. The idea is that I am trying to be able to show context for some data. For example, we have some scoring data for our customer service group and at the moment, a chart with our score won't mean anything to those who don't have the context of what the scoring range is. I want to be able to use a gauge chart to provide that visual context as to whether the current score is bad (red), mediocre (yellow), good (green).

Does that explanation help you?

Paul Newcome

What if you were to use a RYG Harvey ball as well as the number in a Metrics Widget and then create a basic half-donut chart as a sort of visual next to it?

Paul Newcome

Oh. And a gauge chart can be created in excel using a combination of a pie chart and a donut chart and some rather creative formatting.

Kelly Gabel

That's a distinct possibility (the first option...that looks great actually! How do I make that?

And re: the Excel comment, yes I did see that workaround to make the gauge chart in Excel but I don't have the time today to understand that creative formatting!! LOL Especially since it was a mock up anyway...I'd rather learn how to mimic in Smartsheet :)

Joey Razzano

Sorry to butt in but you can choose the half circle chart type in the widget on your dashboard - makes a gauge quite nicely :)

Kelly Gabel

@joey.w.razzano33566 Thanks for chiming in!! I did consider that but I was struggling to make it report the correct data to actually work as a gauge. Do you have an example where you've used it that way?

Kelly Gabel

@Paul Newcome so I need to make the ranges for your example like this:

If the number is between -100 and 0, make it red, between 0 and 30 make it yellow, 30-70 make it light green, and 70-100 make it dark green.

I have no clue how to do this in Smartsheet! I assume that this would be a new column on my sheet, but would I need one next to each month for comparison or is it possible to do one column and then just compare it to the current month?