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

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
Asked:
Ryan Simmons
  • 4
  • 2
1 Solution
 
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
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
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
 
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
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.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now