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
Ryan SimmonsBusiness Analyst IIIAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder 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
Mike in ITIT System AdministratorCommented:
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
Ryan SimmonsBusiness Analyst IIIAuthor Commented:
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Ryan SimmonsBusiness Analyst IIIAuthor Commented:
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
Ryan SimmonsBusiness Analyst IIIAuthor Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mike in ITIT System AdministratorCommented:
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)

Open in new window


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

Open in new window


so that it will give me a running total of how many 15-minute chunks I still have.
0
Ryan SimmonsBusiness Analyst IIIAuthor Commented:
Closing the question so I can ask a better question which will be easier for the experts to answer.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Math / Science

From novice to tech pro — start learning today.