Health Ball Color Based on progress

Daxal · 03/31/22

Hello I have =IF(OR([COS 2022 ACT - YTD]@row > 0.05 + [COS 2021 Baseline]@row, "Green", IF([COS 2022 ACT - YTD]@row > 0.1 + [COS 2021 Baseline]@row, "Yellow", IF([COS 2022 ACT - YTD]@row > 0.15 + [COS 2021 Baseline]@row, "Red"))))

COS 2021 Baseline COS 2022 Target COS 2022 ACT - YTD

Can you please help me with this: over all planned improvement is .5 then by June it should have improved by 0.25 + 5% for green 0.25 + 10% Yellow and 0.25 + 15% Red

Genevieve P. · 04/04/22

Hi @Daxal

There are a couple of details to adjust in your formula. When you use an OR statement, you'll want to make sure you close off the OR Function OR() <- before then telling it what to do.

=IF(OR(This, or That), "Green"

However based on your description, it doesn't sound like we need an OR at all. You can simply state what you want the formula to look for, since there is only one value to search for.

The second detail to note is that you are adding 0.05 to a value but you're also looking to see if a cell is > than a value. There currently isn't a way for your formula to tell what you want to do first... the > or the +. See: Create and Edit Formulas in Smartsheet

Try wrapping your + statements in their own (parentheses), like so:

[COS 2022 ACT - YTD]@row > (0.05 + [COS 2021 Baseline]@row), "Green",

Finally, IF Function statements will stop as soon as they find a match. This means that if a cell is > 0.1 (greater than 0.1) it will also be > 0.05 (greater than 0.05), so we need to make sure we're putting the statements in the right order. Start with your 0.15, first:

Adjusted Formula:

=IF([COS 2022 ACT - YTD]@row > (0.15 + [COS 2021 Baseline]@row), "Red", IF([COS 2022 ACT - YTD]@row > (0.1 + [COS 2021 Baseline]@row), "Yellow", IF([COS 2022 ACT - YTD]@row > (0.05 + [COS 2021 Baseline]@row), "Green"))))

Does this give you the output you're looking for? If not, could you be more specific with what you want each statement to do?

Cheers,

Genevieve

Genevieve P. · 04/04/22