IF, RYG and range

Michaela Kamenska

Hi All,

I'm growing absolutely desperate. I have gone through what feels like a milion various posts on here, but nothing quite seems to answer what I need. I am trying to set up RYG conditions based on monthly estimated FTEs so that if the FTE is higher than 1 in any of the columns, the ball goes red.

And I am wondering if anyone can make it simpler? A nice to have would also be to have a condition that if the value in any of the columns in the row is higher than 0.75 to turn it yellow. I'm happy to add helper columns if that is needed.

Now, I have this formula that works, but it appears to be too complicated (please note I have 27 of these columns):

=IF([Oct-20]@row >= 1, "Red", IF([Nov-20]@row >= 1, "Red", IF([Dec-20]@row >= 1, "Red", IF([Jan-21]@row >= 1, "Red", IF([Feb-21]@row >= 1, "Red", IF([Mar-21]@row >= 1, "Red", IF([Apr-21]@row >= 1, "Red", IF([May-21]@row >= 1, "Red", IF([Jun-21]@row >= 1, "Red", IF([Jul-21]@row >= 1, "Red", IF([Aug-21]@row >= 1, "Red", IF([Sep-21]@row >= 1, "Red", IF([Oct-21]@row >= 1, "Red", IF([Nov-21]@row >= 1, "Red", IF([Dec-21]@row >= 1, "Red", IF([Jan-22]@row >= 1, "Red", IF([Feb-22]@row >= 1, "Red", IF([Mar-22]@row >= 1, "Red", IF([Apr-22]@row >= 1, "Red", IF([May-22]@row >= 1, "Red", IF([Jun-22]@row >= 1, "Red", IF([Jul-22]@row >= 1, "Red", IF([Aug-22]@row >= 1, "Red", IF([Sep-22]@row >= 1, "Red", IF([Oct-22]@row >= 1, "Red", IF([Nov-22]@row >= 1, "Red", IF([Dec-22]@row >= 1, "Red", "Green")))))))))))))))))))))))))))

Kelly Moore

Hello Michaela

If I understand correctly, if any of the cells in a row are 1 or above, then the status should be Red

I think you can achieve this with a CountIf statement

This formula would go in your Status column. The range is needs to be adjusted to include all of your month columns

=IF(COUNTIF([your 1st month]@row:[your last month]@row, 1) > 0, "Red", "Green")

This says to count the number of cells in the range that contain "1", and if the count is greater than zero (which means it found at least 1 instance in the row), the status should be Red. Otherwise, it's Green. Be sure to change the column names to your actual column names.

Hope this helps

Kelly

David Joyeuse

Hi @Michaela Kamenska

That's doable obviously:

=IF(COUNTIF([Oct-20]@row:[Dec-22]@row, >=1) > 0, "Red", IF(COUNTIF([Oct-20]@row:[Dec-22]@row, >=0.75)>0, "Yellow", "Green"))

Hope it helped!

Kelly Moore

Hello Michaela

If I understand correctly, if any of the cells in a row are 1 or above, then the status should be Red

I think you can achieve this with a CountIf statement

This formula would go in your Status column. The range is needs to be adjusted to include all of your month columns

=IF(COUNTIF([your 1st month]@row:[your last month]@row, 1) > 0, "Red", "Green")

This says to count the number of cells in the range that contain "1", and if the count is greater than zero (which means it found at least 1 instance in the row), the status should be Red. Otherwise, it's Green. Be sure to change the column names to your actual column names.

Hope this helps

Kelly

Michaela Kamenska

Hi Kelly,

Thank you, that worked!

Is there a way I could also add another argument to the formula, where anything within the range of 0.75 - 1 turns yellow? I'm just really not good at joining multiple formulas and arguments, so if you can help, that'd be great!

David Joyeuse

Hi @Michaela Kamenska

That's doable obviously:

=IF(COUNTIF([Oct-20]@row:[Dec-22]@row, >=1) > 0, "Red", IF(COUNTIF([Oct-20]@row:[Dec-22]@row, >=0.75)>0, "Yellow", "Green"))

Hope it helped!

Michaela Kamenska

@David Joyeuse Thank you so much!

I am so sorry as it may look like a "not very smart" question for many, but I do get errors often when combining multiple arguments and formulas!

Kelly Moore

Michaela

Your questions are good questions, and keep them coming! We all learn a lot from each others questions. If you have additional errors in your formulas, post another question. The community is glad to help out.