Formula for prioritization

celeronhater

New Member
Hey guys,

I hope you are all doing well. I have a quick question related to an applied research I need to make for my job. Basically, we have a team of 5 people and we are doing different tasks for customers based on the priority of the tasks. So we are having a random order every day (each one of us needs to take at least one of the first 5 tasks so that it can be equally distributed). The tasks are having priorities from P1 to P4 as P1 is a critical task while P4 is a non-critical and can be completed in the next several days. I want the work to be equally distributed based on three factors:

- the order from the previous day (I do not want the guy that started first to be first tomorrow as this can lead to him taking more tasks than the others)
- the priority of the case
- the tasks in the backlog of each of us (if there is a colleague that has 15 old tasks and another that has one, I would prefer the colleague that has only one task to take care of the new task and not the colleague with the 15 old tasks)

I have not yet figured out weights but probably 33.33% for each criteria is fine. Could you help me figure out a formula for this task and I will try to automate the process with some UI form which will send the output to a spreadsheet where I can analyze the results. I would like the formula to be able to determine the row to pickup tasks for each consecutive day.

Thanks !