RAG Changes to Monitor Cooler and Freezer

Vinton Douglas

Hi All-

I have a single dropdown to monitor the temperatures of 2 Coolers and 2 Freezers from incoming forms submitted.

Cooler 1

Cooler 2

Freezer 1

Freezer 2

If the temp for coolers are <=40F, I want the status bar to return green, otherwise red

if the temp for the freezer <=0F, I want the status bar to return green, otherwise red

Is it possible to achieve the results on one incoming spreadsheet?

Gia Thinh

Hi @Vinton Douglas,

Yes , it is possible. Try this sheet with below formula:

=IF(CONTAINS("Cooler", Products@row), IF(Temperature@row <= 40, "Green", "Red"), IF(CONTAINS("Freezer", Products@row), IF(Temperature@row <= 0, "Green", "Red")))

Hope that helps.

Gia Thinh

In fact, the formula should be working for Cooler 3/4/5/... or Freezer 3/4/5/... because we just check the Cooler text or Freezer text in the Products column.

Can you post your screenshot so that we can check it out?

Andrée Starå

Hi @Vinton Douglas

I hope you're well and safe!

Try something like this.

=IF([Temperature (F)]@row = "", "", IF(CONTAINS("Cooler 1", [Refrigerator Name/Number]@row), IF([Temperature (F)]@row <= "0", "Green", "Red"), IF(CONTAINS("Cooler 1", [Refrigerator Name/Number]@row), IF([Temperature (F)]@row <= "0", "Green", "Red"), IF(CONTAINS("Freezer", [Refrigerator Name/Number]@row), IF([Temperature (F)]@row >= "-40", "Green", "Red")))))

Did that work?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Gia Thinh

Hi @Vinton Douglas,

Yes , it is possible. Try this sheet with below formula:

=IF(CONTAINS("Cooler", Products@row), IF(Temperature@row <= 40, "Green", "Red"), IF(CONTAINS("Freezer", Products@row), IF(Temperature@row <= 0, "Green", "Red")))

Hope that helps.

Vinton Douglas

Thank you very much for your response. It works great! I tried adding another cooler and freezer but its not working. Could you add another aspect to the formula if I want to add Cooler 3 and Freezer 3?

Gia Thinh

In fact, the formula should be working for Cooler 3/4/5/... or Freezer 3/4/5/... because we just check the Cooler text or Freezer text in the Products column.

Can you post your screenshot so that we can check it out?

Vinton Douglas

Hi Gia,

Thanks much. I created the nested formula and it works great. The only issue is if this is populated on a spreadsheet, the RAG status reflects green or red even if the space under temperature is blank. See formula:

=IF(CONTAINS("Cooler 1", [Refrigerator Name/Number]@row), IF([Temperature (F)]@row <= "0", "Green", "Red"), IF(CONTAINS("Cooler 1", [Refrigerator Name/Number]@row), IF([Temperature (F)]@row <= "0", "Green", "Red"), IF(CONTAINS("Freezer", [Refrigerator Name/Number]@row), IF([Temperature (F)]@row >= "-40", "Green", "Red")))...

How can I tweak the formula so that when the temperature is blank, the RAG status remains blank?

Andrée Starå

Hi @Vinton Douglas

I hope you're well and safe!

Try something like this.

=IF([Temperature (F)]@row = "", "", IF(CONTAINS("Cooler 1", [Refrigerator Name/Number]@row), IF([Temperature (F)]@row <= "0", "Green", "Red"), IF(CONTAINS("Cooler 1", [Refrigerator Name/Number]@row), IF([Temperature (F)]@row <= "0", "Green", "Red"), IF(CONTAINS("Freezer", [Refrigerator Name/Number]@row), IF([Temperature (F)]@row >= "-40", "Green", "Red")))))

Did that work?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!