+ Reply to Thread
Results 1 to 17 of 17

Counting days worked and Averaging Totals

  1. #1
    Registered User
    Join Date
    08-16-2004
    Posts
    8

    Question 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

  2. #2
    Registered User
    Join Date
    08-16-2004
    Posts
    8

    Sorry the example got crammed together

    Sorry, but the example got crammed together.


    grrr

    Lilbpaw

  3. #3
    Forum Expert Carim's Avatar
    Join Date
    04-07-2006
    Posts
    4,071
    Hi,

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

    HTH
    Carim

  4. #4
    Registered User
    Join Date
    08-16-2004
    Posts
    8

    No Pivot Table

    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

  5. #5
    Forum Contributor
    Join Date
    10-27-2006
    Location
    United Kingdom
    MS-Off Ver
    EXCEL 2003 (2007 at home)
    Posts
    517
    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.

  6. #6
    Registered User
    Join Date
    08-16-2004
    Posts
    8

    I did a breif reading

    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

  7. #7
    Forum Expert Carim's Avatar
    Join Date
    04-07-2006
    Posts
    4,071
    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

  8. #8
    Registered User
    Join Date
    08-16-2004
    Posts
    8

    Uploading the worksheet

    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

  9. #9
    Forum Expert Carim's Avatar
    Join Date
    04-07-2006
    Posts
    4,071
    Hi,

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

    HTH
    Carim

  10. #10
    Registered User
    Join Date
    08-16-2004
    Posts
    8

    Finally got it

    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
    Attached Files Attached Files

  11. #11
    Forum Expert Carim's Avatar
    Join Date
    04-07-2006
    Posts
    4,071
    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

  12. #12
    Registered User
    Join Date
    08-16-2004
    Posts
    8

    Sorry

    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

  13. #13
    Forum Expert Carim's Avatar
    Join Date
    04-07-2006
    Posts
    4,071
    What is wrong with your present formula ...?
    What is the result you are expecting ...?

    Carim

  14. #14
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Hi,

    An example of the pivot view

    VBA Noob
    Attached Files Attached Files
    _________________________________________


    Credo Elvem ipsum etian vivere
    _________________________________________
    A message for cross posters

    Please remember to wrap code.

    Forum Rules

    Please add to your signature if you found this link helpful. Excel links !!!

  15. #15
    Forum Contributor
    Join Date
    07-05-2006
    Location
    Canada
    MS-Off Ver
    2003, 2007
    Posts
    581
    The simple way would probably be:
    =SUM(B3:M3)/(IF(SUM(B3:E3),1)+IF(SUM(F3:I3),1)+IF(SUM(J3:M3),1))

    It's not that elegant, but it should work. (I just used the days you had. You'll need to add a bit to the denominator to account for all 7 days.)

    Scott

    The full formula is probably:

    Please Login or Register  to view this content.
    Last edited by Maistrye; 11-21-2006 at 03:10 PM. Reason: Add full formula

  16. #16
    Forum Contributor
    Join Date
    07-05-2006
    Location
    Canada
    MS-Off Ver
    2003, 2007
    Posts
    581
    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

  17. #17
    Registered User
    Join Date
    08-16-2004
    Posts
    8

    Talking Yay!

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1