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