• Status: Solved
• Priority: High
• Security: Public
• Views: 61

# EXCEL: Evenly Distribute 0.25 time where it is most needed represented by avg number of tickets across QH intervals. Example inside.

I would like to distribute the difference between the weekly labor target G2 and the weekly hours of operation I2 in even 0.25 chunks. The difference between G2 and I2 is in J2. If you multiply J2 by four you can see that I have 82 0.25 hour chunks of time available to distribute. How can I distribute those 82 chunks based on the needs represented by the ticket counts (workload) represented in column F. It's okay for some rows to be blank as long as I don't exceed the 82 chunks of time available and distribute the 0.25 hours to the intervals where the needs are highest.

I have tried distributing the labor based on a ratio defined by F/G but that often results in uneven labor or labor that is blowing the budget available.
Example_LaborDistribution.xlsx
0
Ryan Simmons
• 4
• 2
1 Solution

Older than dirtCommented:
Are you saying in your example that 82 rows should be added between rows 2 and 3?

I think that an "after" picture for row 2 would help.
0

What you are asking doesn't make sense. You cannot distribute time, you can distribute tickets across time. The workbook that you provided doesn't help with understanding what you are looking for. The only numbers that are changing are the AvgTicketCount and the date and time. What are we supposed to be distributing? You already have the time laid out, you cannot add time to any line you have there.

Can you give an example of what you are hoping to accomplish?
0

No problem let me explain it this way. If you look at the example worksheet you will see one store opened during their hours of operation for one week. The AvgTicketCount represents the amount of work the store does in each interval. If you sum the column Open? you can see that the store is open 125.5 hours. The 0.25 means that a 1/4 hour of labor is needed to just keep the doors open. A employee must be staffed during that quarter hour. I want to give the store 20.5 hours in 0.25 hour chunks. 20.5*4 converts hours to quarter hours. This shows me that the store has 82 0.25 hours that I can distribute.

I am seeking a method that would allow me to have a formula look at the AvgTicketCount and then put 0.25 hours in the rows where they are most needed by the store. an additional 0.25 hours would allow the store to have an additional employee staffed.

I have attached a new version of the file with a mockup in Column L.
Example_LaborDistribution.xlsx
0

I think that rank is part of the solution. If I put the rank function in column L and tell it that the array is avg ticket count and I sort the column it will rank the AvgTicketCount from highest to lowest.
0

Attached is example with Rank Function. Working out how to apply the labor from highest rank number to lowest while also automatically keeping the number of 0.25 hours to just 82 total opportunities.
Example_LaborDistribution.xlsx
0

Let me see if I understand this. You are wanting to assign people to work in 15-minute chunks, but you only want to use 82 of them because you already have people working that will cover part of the work for each 15-minute chunk. So at what point do you need more people working? After there are 11 tickets? If that is the case then you could use a formula like:

``````=IF(F2>11,F2-11,0)
``````

And on the first day, you would have 66 15-minute chunks left over at the end of the day because most time slots you would not need extra workers.

It kind of looks like you are trying to schedule hours for workers.

PS: in column K I put:
``````=K2-L3
``````

so that it will give me a running total of how many 15-minute chunks I still have.
0

Closing the question so I can ask a better question which will be easier for the experts to answer.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.