# Function best for Evaluations

Options
✭✭✭

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?

• ✭✭✭✭✭✭
Options

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"

• ✭✭✭✭✭✭
Options

=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"

• ✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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"

• ✭✭✭
Options

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!