Automated Break Schedule & Meeting

Hi,

We are seeking help in automating our break schedule & Meeting activity.

What we would like to happen is have excel automate the break schedule (1st break, Lunch, 2nd Break) & the Meeting (Offline activity) all this will be based on the attendance that the Team leads sends.

We would like to have excel automate the break schedule based on a weekly requirements sent to us by our clients.
making sure we wont go below that requirements.

-You will find the all the tabs in there.
-Attach is a sample excel for your reference View attachment RTA Forecaster v1.xlsx

So which Formula(e) do you need help with, and what is the problem with it(them)?

Hercules1946 said:

So which Formula(e) do you need help with, and what is the problem with it(them)?

Click to expand...

Our Dilemma is its not calculating the total number of staff (agents) when the break schedule is being loaded in the Breaks and Lunches tab
What we are hoping is to have excel auto calculate the total staff number of staff left whenever a batch goes on Break and or Lunch or even Meeting (for agent develpment)
taking into account that we wont go below our minimum requirement and wont go beyond the maximum requirement. The minimum requirement is the "Commit" and the Maximum requirement should be the "R.Demand". As what you can see in the template someone already started to roll things up a little but its not their yet.

We wanna eliminate the minimum margin of error as much as we can. So if we can at least forecast the total staff every 30 minutes based on the attendance of the site. We can at least avoid being penalize by our Clients.

dlealb said:

As what you can see in the template someone already started to roll things up a little but its not their yet.

Click to expand...

I think that your best bet might be to get your "someone" to progress the task for you. I could be wrong, but I don't think you will find anyone in this type of forum that will take on the project at the stage you are, except on a professional basis. Obviously, and with your involvement, it will take a fair amount of time (from cold) to understand what is going on in your workbook, before any amendments/enhancements could be attempted.
On the other hand if you can break it down into components, and focus more directly on what you want to add, it might be possible to make some progress.
Hope that helps.

Number of agents taking Break are not being counted

Hercules1946 said:

I think that your best bet might be to get your "someone" to progress the task for you. I could be wrong, but I don't think you will find anyone in this type of forum that will take on the project at the stage you are, except on a professional basis. Obviously, and with your involvement, it will take a fair amount of time (from cold) to understand what is going on in your workbook, before any amendments/enhancements could be attempted.
On the other hand if you can break it down into components, and focus more directly on what you want to add, it might be possible to make some progress.
Hope that helps.

Click to expand...

As you guys can see in the sample below the number of agents are not being counted according to their number of headcounts.
Sample in 2:00 PM it only deducts 2 agents when infact they are 25 agents. If we can get this template to calculate the number of agents going in and out this would help us in getting ready for the next 30 minutes.

Can you post the formula that calculates the deduction of -2, together with the data used by the formula ?

Hercules1946 said:

Can you post the formula that calculates the deduction of -2, together with the data used by the formula ?

Click to expand...

This is the formula in 01:00 PM interval.

=(SUMPRODUCT(('Breaks and Lunches'!$A$2:$A$86='Current Staffing'!H$5)*($A20>='Breaks and Lunches'!$AB$2:$AB$86)*($A20<'Breaks and Lunches'!$AC$2:$AC$86)*('Breaks and Lunches'!$X$2:$X$86))+SUMPRODUCT(('Breaks and Lunches'!$A$2:$A$86='Current Staffing'!H$5)*($A20>='Breaks and Lunches'!$AD$2:$AD$86)*($A20<'Breaks and Lunches'!$AE$2:$AE$86)*('Breaks and Lunches'!$Y$2:$Y$86))+SUMPRODUCT(('Breaks and Lunches'!$A$2:$A$86='Current Staffing'!H$5)*($A20>='Breaks and Lunches'!$AF$2:$AF$86)*($A20<'Breaks and Lunches'!$AG$2:$AG$86)*('Breaks and Lunches'!$Z$2:$Z$86))+SUMPRODUCT(('Breaks and Lunches'!$A$2:$A$86='Current Staffing'!H$5)*($A20>='Breaks and Lunches'!$AH$2:$AH$86)*($A20<'Breaks and Lunches'!$AI$2:$AI$86)*('Breaks and Lunches'!$AA$2:$AA$86)))/30*H$4*(-1)

View attachment RTA Forecaster v1.xlsx
the data should be in the file.
The breaks are coded in this manner.

I spent some time looking at this, and I was unable to locate the 01:00PM interval formula from your last post anywhere in the attachment workbook. The formula references A20, which should be in the same worksheet, and if its not then the formula will return zero.
I was trying to use the "evaluate formula" facility on the formula ribbon, but without the correct location for the formula, this can't be done.
I suggest that you try it after making sure that you have open all the data that the formula is using, and the formula itself in the correct location. This will show you how the formula is translated into a result step by step, and you should be able to work out whats going wrong.

I have now managed to find where the formula you posted is in the workbook, and while I dont fully understand all the bits of data, I should be able to interprete the formula given a little time.
You have a large number of TRUE/FALSE outcomes and within each SUMPRODUCT just one FALSE is enough to return zero.

The formula you posted in #7 is trying to calculate a deduction for breaks and lunches, but it seems to be widely located in the grey sections of the "Current Staffing" ("CS") sheet, where results are not readable.
The formula gives results if copied into the yellow block H5572, mainly because numbers read from Col A start to match correctly with the tables in "Breaks and Lunches" ("B&L"), and the correct team names, e.g. "Batch 1", can be matched with Row 5.
The formula processes an 8 column table to determine if the value taken from Col A matches correctly with values in Cols AB to AI ("B&L") for the team in question. Many of the values from Col A are out of range, so a zero is returned.
If there is a match, then the corresponding number from Cols X to AA will be summed into 1 of the 4 SUMPRODUCT functions. These might be 10, 30, 10 or 0 depending on which of the 4 SUMPRODUCTS match.
The 4th elements, Cols {AH, AI}, and Col AA ("B&L") have no supporting data, so the 4th SUMPRODUCT in the formula can only return 0.
The returned values are then divided by 30, giving the fractions 1/3rd, 1, 1/3rd, or 0. Then, these fractions are multiplied by the value in Row 4 of the formula column labelled Present HC ("CS"). So, in Col H, H4 = 15), so the result would be either 1/3rd *15, 1*15, 1/3rd *15 or 0 in Col H.
These figures are finally turned negative, so the final result in H55 to H72 will be either -5, -15, -5 or 0

I hope this will help you to understand the formula better and you can spot what is wrong.