Formula help

Hello,

I am needing assistance with the formula column. I am currently making a board that measures various metrics in various formats. Some are seconds, hours, percentages etc. The goal is to have a formula that advises the “health” of the metric based on its parameters. The first part of the formula needs to meet the below parameters:
Minimum value - 95
Expected value - 99
Actual value - 96
Health - based on the formula, would show “Green” because the actual value met the minimum value.

The second part of the formula needs to meet these parameters:
Minimum value - 95
Expected value - 99
Actual value - 94
Health - based on the formula, would show “Red” because the actual value did not meet the minimum value.

The minimum & expected values on each line item are different, so not every one is 95 & 99, and that is where I am running into trouble. I know that it has to be at the column label level (minimum value, expected value, actual value) and possibly dealing with a “greater than, less than” approach, but I am stuck.

Thanks in advance!

Hi Carly,

So, you have 3 columns (minimum, expected and actual) with values in them and you want to calculate the item’s health?

Hi Gilles,

Yes, there would be another column titled Health and the values in that column would reflect:

Green if the actual was greater than or equal to expected
Yellow if it was greater than or equal to minimum but less than expected
Red if it was less than the minimum.

If you don’t want to use a 3rd-party app, you’ll have to use a Formula column and use a coloring condition to get it colored.

The formula could be something like this:

IF({actual}>={expected},"Green",IF({actual}>={minimum},"Orange","Red"))

Which would result in:

Provided you set the condition filter to be:

Now, if you can use a 3rd-party app called the Advanced Formula Booster, you could get the result to modify a real status column.

Here would be the syntax of the formula:

and here would be the result:

Hope it helps.