Formula to create symbols based on parameters

Bryan Letourneau

Hello Community,

I'm attempting to create a formula that will create a green, red, or yellow dot symbol based on parameters and values within given cells.

The formula I have thus far is below but I continue to receive error messages

Any help would be appreciated.

Thanks!

Screenshot 2022-05-05 133202.jpg

Mike TV

@Bryan Letourneau

I think I tested out all scenarios. If you find anything out of sorts and can't figure it out yourself, just let me know whether Auto is on or not and what temp it's not acting correctly with.

Example sheet:

Status formula:

=IF(AND([ARU1 HMI in Auto]@row = "Auto", [ARU1 Room Temp]@row >= 65, [ARU1 Room Temp]@row <= 77), "Green", IF(AND([ARU1 HMI in Auto]@row <> "Auto", [ARU1 Room Temp]@row >= 65, [ARU1 Room Temp]@row <= 77), "Yellow", IF([ARU1 Room Temp]@row < 65, "Red", IF([ARU1 Room Temp]@row > 77, "Red", ""))))

Mike TV

@Bryan Letourneau

It looks like you've got conflicting formulas and the IF statement at the end doesn't have a value for true set.

Your 1st IF statement says to mark a Red symbol if temp is greater than 77.

Your 2nd IF statement says to mark a Red symbol if temp is less than 35.

Your 3rd IF statement says to mark a Green symbol if temp is less than or equal to 35 (conflicts with 2nd IF). Do you want it Green or Red if less than 35?

Your 4th IF statement has a comma after designating the cell to look at so it doesn't finish the logic statement, even without the comma there it doesn't set a condition for what to do if that cell is equal to or greater than 77.

If you explain clearly what color results you want for different temperature ranges, I can help write the formula you'd need.

Bryan Letourneau

Thanks Matt, I noticed the IF statement issue immediately following my submission. But let me know if the below is sufficient.

Green Symbol- If "ARU1 HMI in Auto" is in Auto, and "ARU1 Room Temp" is between 65 and 77

Yellow Symbol- If "ARU1 HMI in Auto" is NOT in Auto (Offline or Manual), and "ARU1 Room Temp" is between 65 and 77

Red Symbol- If "ARU1 Room Temp" is NOT between 65 and 77 (Irrespective of ARU1 HMI in Auto)

Let me know if you need additional information and thanks again for your help!

Mike TV

@Bryan Letourneau

I think I tested out all scenarios. If you find anything out of sorts and can't figure it out yourself, just let me know whether Auto is on or not and what temp it's not acting correctly with.

Example sheet:

Status formula:

=IF(AND([ARU1 HMI in Auto]@row = "Auto", [ARU1 Room Temp]@row >= 65, [ARU1 Room Temp]@row <= 77), "Green", IF(AND([ARU1 HMI in Auto]@row <> "Auto", [ARU1 Room Temp]@row >= 65, [ARU1 Room Temp]@row <= 77), "Yellow", IF([ARU1 Room Temp]@row < 65, "Red", IF([ARU1 Room Temp]@row > 77, "Red", ""))))

Bryan Letourneau

You are truly a wizard, thank you so much!