Function best for Evaluations

This snapshot is from our company's evaluations of our employees.

Now we are looking to populate the "MARKS" Column. The equation should read in the Attendance@row:Efficiency@row cell range, if it finds "Excellent" it populates a Green Status Bubble, if it finds "Good" it populates a Gray Status Bubble, if it finds "Fair" it populates a Yellow Status Bubble, and if it finds "Poor" it populates Red Status Bubble.

If there isn't an exact function, is there a workaround?

Best Answer

  • L_123
    L_123 ✭✭✭✭✭✭
    Answer ✓

    ok, you just need to flip the order of the formula then

    =if(countif(Attendance@row:Efficiency@row,"Poor")>0,"Red",if(countif(Attendance@row:Efficiency@row,"Fair")>0,"Yellow",if(countif(Attendance@row:Efficiency@row,"Good")>0,"Grey","Green"

Answers

  • L_123
    L_123 ✭✭✭✭✭✭

    =if(countif(Attendance@row:Efficiency@row,"Excellent")>0,"Green",if(countif(Attendance@row:Efficiency@row,"Good")>0,"Grey",if(countif(Attendance@row:Efficiency@row,"Fair")>0,"Yellow","Red"

  • ksandoval
    ksandoval ✭✭✭

    Thanks, the equation worked, it is closer to what we need, but not quite there yet.

    On the second row, the status ball turns green, but it should be yellow because of the "Fair". On the sixth row, the bubble is Gray, but it should be red because of the 1 "poor".

    We are trying to make it where if you get all Excellent then Green status bubble, if you receive 1 good on anything then Gray status bubble, if you receive 1 fair then yellow status bubble, and if you receive 1 poor then red status bubble

  • L_123
    L_123 ✭✭✭✭✭✭
    Answer ✓

    ok, you just need to flip the order of the formula then

    =if(countif(Attendance@row:Efficiency@row,"Poor")>0,"Red",if(countif(Attendance@row:Efficiency@row,"Fair")>0,"Yellow",if(countif(Attendance@row:Efficiency@row,"Good")>0,"Grey","Green"

  • ksandoval
    ksandoval ✭✭✭

    Thanks a lot. This is what we have been looking for.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!