Guided project · 6 steps Cellwise Plus
Picker productivity report in Excel
Turn a week of picking hours, lines and errors into pick rates, accuracy, a target check and the team rate.
You will use: ROUND, IF, AND, SUM, MAX, MATCH, INDEX, COUNTIF
Starting data
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Picker | Hours | Lines picked | Errors | Lines/hour | Accuracy | Check | Target lines/hour | 30 | |
| 2 | Ana | 38.5 | 1240 | 6 | Target accuracy | 0.995 | ||||
| 3 | Ben | 40 | 1105 | 3 | Team lines/hour | |||||
| 4 | Chen | 22 | 760 | 9 | Fastest picker | |||||
| 5 | Dev | 39 | 1010 | 2 | Meeting both targets | |||||
| 6 | Eli | 16 | 380 | 1 | ||||||
| 7 | Fay | 37.5 | 1180 | 4 |
Build it step by step
Step 1
Pick rate is lines picked divided by hours, rounded to 1 decimal. Write it in E2 and fill to E7.
Step 2
Accuracy is the share of lines picked without an error: 1 minus errors divided by lines. Write it in F2 and fill to F7.
Step 3
In G2, show "Meets" when the pick rate reaches J1 AND accuracy reaches J2, otherwise "Coach". Fill to G7.
Step 4
What is the team pick rate? Divide total lines by total hours, rounded to 1 decimal, in J3.
Step 5
Who is the fastest picker? Put the name in J4.
Step 6
How many pickers meet both targets? Count them in J5.