Counting days worked and Averaging Totals

This is more complicated than it sounds and I could really use some help. I have been working on just this one formula for days.... grrr

I am working on an Spread Sheet that contains about 50 names. The people on the list each pull a job from the system and process the job, then they enter the job status and product information into a tracker. I then pull the data from the tracker and enter it into my Spread Sheet. My spread sheet will then analyze and calculate how many jobs, they did that week.

Where does it get complicated. Well, when they enter the information into the tracker there are 4 different statuses they can choose from. They can choose, Completed, Non-Completed, Revised Completed, Revised Uncompleted.

My sheet breaks down not only how many orders they did, but what status they were. The problem occurs because when you look at the spread sheet, you will see the days of the week, then under it a name and to the right, the jobs in the 4 different status categories. When I try to do an average of jobs per per day, it is actually reading each one of the four categories as a day instead of recognizing that the four categories are actually one day.

I am attempting to First count 1 if they worked that day, then add the total amount of orders, then average how many orders per day they worked for the week. We work 7 days a week with two days off, Each employee having different days off.

Let me see if I can make an example

monday Tuesday AVE P/ DAY
COM RCO RNC UNC COM RCO RNC UNC
Joan 9 2 1 3 1 6 1 2 12.5

Please take note that it needs to look at all days and count how many days that person worked that week as some of them only work 3 or 4, while others work 5 days a week. Also if a person calls in sick I do not want to have to manually change how many days each person worked that week.


I would appreciate any help that you might be able to give me.


Thank you,
Lilbpaw

Sorry, but the example got crammed together.


grrr

Lilbpaw

Hi,

Have you already tried to create a simple Pivot Table of your data ...?

HTH
Carim

I have not created a Pivot Table for my data. This is not something I have learned yet, or experimented with. I did not see at the time how this would help my situation as my Sheet is almost done except this one formula.

I could be wrong as I have no Experience with Pivot Tables, but would this be something I need to learn to fix this sitation?

Lilbpaw

I agree that it is annoying the way this forum strips "superfluous" spaces.
I also have not tried pivot tables, however from the number of times they crop up in this forum I think this is a topic I should take the time to learn.
There are other ways, using the lookup and reference functions (in particular offset) but they can get very complicated.
Mark.

on pivot tables and I don't see how this would help me find the formula to my problem.
I have all the formulas to lay out the data exactly how I want, just not the one to find out how to count the 4 different status categories as one day so that I can get a daily average of tickets worked.

Frustrating... ugh

Lilbpaw

The function which will provide your answer is
=sumproduct()
since you can have as many criteria as you need ...

For a tailor-made answer, just upload your worksheet in a zipped format ...

HTH
Carim

The formula I need is in row aj.. such as aj3 and aj15. They will hold the average Orders Per Day calculated from the totals in the corresponding blue rows for each employee.

I have down sized it as much as I can, however it is still saying it is 221 kb which exceeds the limit of 100 kb.
Grr..
I am still trying though

Lilbpaw

Hi,

Do not forget to zip your file ...
it will drastically reduce its size ...

HTH
Carim

ok, i deleted everything possible.
The formula would be for this one employee that i can use to reference for the other employees. I hope.

The cell that needs calculation is now O3. It must figure out if the employee worked that day, how many orders were done and average them out to a daily average. Each day has 4 categories that must be seen as one day.

I hope that is not confusing.

Thank you for your help.

Lilbpaw

At first sight it is a bit confusing since the sheet Data which I suppose is tracking orders does not have any reference to the different individuals you want to track ...


Carim

I thought I saved the data in the data tab and the names in the formula tab.
then the days with the categoreis of rts, rev-rts, rev-nrts and nrts.
I had to cut out a lot of the days so that I could make the file small enough for it to upload.
This was quite difficult.
the field under OPD is where it should calculate the average after counting the orders and the days.

I believe I left 3 days, 2 where the employee worked and 1 where they didn't.
Since I have chopped that one to bits you can add anything you like or delete or move.. etc.

However notice how the blue line.. that should extend from their name is the total for each column that contains the catagories. I was trying to use these totals to calculate the average.

I wish i could have uploaded the whole thing to give you a better view of what I am talking about...


grrr.. sorry

What is wrong with your present formula ...?
What is the result you are expecting ...?

Carim

I forgot... this assumes that they had sales each day that they worked. You may still have to do some manual stuff if there is the possibility of someone working on a given day and not having sales.

Scott

Well with a few adjustments and looking at your code, it works!
thank you all so much for your help and input.
I still was confused by the pivot table, however I have saved the copy and an continuing to study it closely and see how it can benefit me in the future.

It maybe a step I want to learn really soon.

Thanks again to all!

Lilbpaw