Function best for Evaluations

ksandoval

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?

L_123

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"

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

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

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

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