Assign Tasks Fairly in Excel When Some Team Members Work Part-Time
If a full-time team member and a three-day-a-week team member receive
tasks from the same round-robin list, the list looks fair while the work
does not.
The useful question is not simply, “Who had the last task?” It
is:
Who has received the least work after adjusting for the amount of
work they are available to take?
This workbook uses synthetic data. It is a teaching example, not a
customer result. A live Jolene.pro result could not be verified.
The example
The practice file has three people:
| Person | Workload weight | Tasks after 24 assignments |
|---|---|---|
| Alex | 100% | 10 |
| Blair | 80% | 8 |
| Casey | 60% | 6 |
The split is proportional to the weights:
- Alex: 10 of 24 tasks, or 41.7%
- Blair: 8 of 24 tasks, or 33.3%
- Casey: 6 of 24 tasks, or 25.0%
The next recommendation is Alex.
Set up the team table
In the practice workbook, the team table starts in
Allocation!A12:H15.
A13:A15contains the names.B13:B15contains the workload weights.C13:C15counts completed tasks from the task log.D13:D15calculates the score used for the next
recommendation.
The task-count formula in C13 is:
=COUNTIFS($B$20:$B$43,A13)
Fill it down to C15.
The next-task score in D13 is:
=IF(OR(B13="",B13<=0),"",(C13+1)/B13)
Fill it down to D15.
The formula adds one to each person’s current task count, divides by
that person’s workload weight, and chooses the lowest resulting
score.
For the checked example:
- Alex:
(10 + 1) / 1.0 = 11.00 - Blair:
(8 + 1) / 0.8 = 11.25 - Casey:
(6 + 1) / 0.6 = 11.67
Alex has the lowest score, so Alex receives task 25.
Return the next person
The recommendation formula in Allocation!B7 is:
=IF(COUNTIFS($H$13:$H$15,"Review weight")>0,"Review weights",INDEX($A$13:$A$15,MATCH(MIN($D$13:$D$15),$D$13:$D$15,0)))
The INDEX and MATCH portion returns the
name beside the lowest score. The first IF stops the
workbook from making a recommendation when a workload weight is missing
or zero.
When two people have the same score, this formula returns the first
one listed. If ties must rotate, add a separate last-assigned date or
sequence field and use it as a tie-breaker.
Keep the history fixed
Use the recommendation to assign the task, then record the person’s
name as a value in the task log.
Do not make old task rows depend on the live recommendation cell. If
every historical row points to B7, the whole task history
changes when the next recommendation changes.
The practice workbook keeps the completed task IDs in
A20:A43 and the recorded assignees in
B20:B43.
To reproduce the checked example without a download, paste this
tab-separated log into Allocation!A20:
T-001 Alex
T-002 Blair
T-003 Casey
T-004 Alex
T-005 Blair
T-006 Alex
T-007 Casey
T-008 Blair
T-009 Alex
T-010 Alex
T-011 Blair
T-012 Casey
T-013 Alex
T-014 Blair
T-015 Casey
T-016 Alex
T-017 Blair
T-018 Alex
T-019 Casey
T-020 Blair
T-021 Alex
T-022 Alex
T-023 Blair
T-024 Casey
Two checks that catch
common mistakes
1. A
blank workload weight should stop the recommendation
Clearing Blair’s weight in B14 changes H14
to Review weight and B7 to
Review weights.
A missing workload weight is not zero capacity and should not
silently produce a normal-looking result.
2. Changing
the task log should change the counts
The supplied task log gives Alex 10 tasks, Blair 8, and Casey 6.
If T-001 is changed from Alex to Blair, the counts
become 9, 9, and 6. The team table updates from the task log. The final
workbook restores the original checked sequence.
When this method is
appropriate
This is useful when:
- tasks are similar enough to count as one unit each;
- workload percentages represent real availability;
- one team member should not receive a full-time share while working a
reduced schedule; and - completed assignments are recorded consistently.
Do not use a simple count when task sizes vary heavily. A two-hour
review and a two-day analysis should not both count as one unit. Add
estimated effort, service value, or another agreed workload measure and
compare weighted effort instead.
Why this is a useful gap
GPTExcel’s current project-timeline material includes resource
allocation and team workload balancing. Rows offers a project to-do
template for tracking tasks, deadlines, and priorities. Those are useful
planning views, but this example answers a narrower operational
question: who should receive the next similar task when team members
have different working patterns?
The checked source cells are the important part. A recommendation
without the task rows and weights behind it is difficult to review when
someone asks why the rotation changed.
Check the workbook with
Jolene.pro
Upload the workbook to Jolene.pro
and ask:
Compare completed task assignments with each person’s workload
weight. Recommend the next person, show the source rows behind each task
count, and flag missing or zero weights.
Jolene.pro is designed to show the workbook cells and rows behind an
answer. This practice example was calculated independently. A live
Jolene.pro run was not available for verification.
For more checked examples, see Jolene.pro
Workbook Studies.
Verification notes
- Synthetic task sequence: 24 assignments.
- Checked counts: Alex 10, Blair 8, Casey 6.
- Checked next-task scores: 11.00, 11.25, 11.67.
- Checked next recommendation: Alex.
- Blank-weight and task-log-change tests passed in the workbook
authoring engine. - The exported workbook was not opened and recalculated in native
desktop Excel. - No live Jolene.pro result was verified.
Sources
- Microsoft INDEX: https://support.microsoft.com/en-us/office/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd
- Microsoft MATCH: https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a
- Microsoft COUNTIFS: https://support.microsoft.com/en-us/office/countifs-function-dda3dc6e-f74e-4aee-88bc-aa8c2a866842
- GPTExcel project timeline use case: https://gptexcel.app/en/use-cases/project-timeline
- Rows project to-do template: https://rows.com/templates/to-do-list
1 comment