Formula to create symbols based on parameters

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!


Tags:

Best Answer

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Answer ✓

    @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", ""))))

Answers

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    edited 05/05/22

    @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.

  • 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
    Mike TV ✭✭✭✭✭✭
    Answer ✓

    @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", ""))))

  • You are truly a wizard, thank you so much!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!