Does anyone know of a resource that helps create a formula with a given data set?

What I ultimately need is:
If we know the hour availability of a given group of people for the week (i.e. employee 1 has 10 hours of availability, employee 2 has 25, etc)
And we know the total hours of work remaining to be assigned (i.e. 125 hours)
How much work should we assign to each person so that they have an equal workload for the week
(I need that net number of hours to assign to each person so that they end up with as equal of availability as possible)
Any thoughts/help?

Have you prompted an AI with this yet?

ChatGPT says the following. Did you already try this and not have it work?

To distribute the workload as evenly as possible among the group of people based on their availability, you can use a formula in Excel. Here's a step-by-step guide on how to do it:
[ol]

Calculate total availability: Sum up the total availability of all employees.

Calculate individual shares: Divide the total work hours by the total availability to determine the "share" of work each availability unit represents.

Allocate work: Multiply each employee's availability by the individual share calculated in step 2.

[/ol]
Here's how you can implement this in Excel:

Assuming your data is structured like this:

• Column A: Employee names
• Column B: Hours of availability for each employee
• Total work hours remaining: Cell D1

You can use the following formula in Column C to calculate the work allocation for each employee:

=IFERROR($D$1/SUM($B$2:$B$10)*B2,"")

Explanation:

• $D$1
  : Total work hours remaining
• SUM($B$2:$B$10)
  : Total availability of all employees
• B2
  : Individual availability of each employee

This formula divides the total work hours by the total availability, then multiplies that ratio by each employee's availability to determine their workload.

Drag this formula down for all employees, and it will calculate the workload allocation for each person. You might want to format the cells as numbers to display the results nicely.

This way, each person will be assigned a workload proportional to their availability, aiming for an equal workload distribution across the team.

I feel like this formula gives each worker a proportionate workload to their total available, no?

Unless I'm misunderstanding the OP, I believe he wants all workers to have as equal of a workload as possible, staying under their available hours? Not necessarily a proportionately equal workload....

Almost positive there is a simpler way to do it, but below should work...could add more "run/attempts" if needed.

Edit cause I clipped columns and rows from last screenshot

Third attempt because formulas got wonky when I tried to copy them down…

To make it cleaner you could do how ever number of attempts you'd think you'd need then just hide the columns for all the ones leading up to it.

I was in a bit of a hurry so did just hard cell/column locks, but if you cleaned those/did them correctly you could copy the formulas over for new columns pretty easily.

I simply took his exact post and put it in ChatGPT. There is always a need to further define and edit when asking ChatGPT.