Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    Jun 2006
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Avg, Min, and Max Extraction (2000 SR-1)

    I have a list of data that displays start time for a client's wait and end time for a client's wait. Hence the wait time is simply the difference. If there is no start time and only an end time, that was the time the client got service and the did not have to wait. There are time blocks that I have dictated in half hour increments. One column shows the time block only if someone waited. This is what I need to use on the summary tab. I am trying to get an Average, Min, and Max wait time based on the block of time people waited in. Example: 9 clients waited in the half hour time block starting at 10am. I someone need to get the average, min and max wait time for just those 9 people. I need a formula(s) that will not rely on that count of 9 clients being static. It will change everyday. Hopefully the sheet will explain clearly enough what I would like to accomplish... Thanks for any help you can give!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Avg, Min, and Max Extraction (2000 SR-1)

    Welcome to Woody's Lounge!

    A pivot table is a very handy tool for this. In the attached zipped version of your workbook, I have added a pivot table based on your data. Most data fields are standard, I only added one calculated field for the percentage of clients that waited.

    Note 1: a pivot table isn't updated automatically. To update it, click in any cell of the pivot table and click the Refresh Data button on the Pivot Table toolbar.
    Note 2: since I'm using the Dutch language version of Excel, some labels in the pivot table are Dutch. You can edit them, or create a new pivot table as an excercise.

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Avg, Min, and Max Extraction (2000 SR-1)

    Hi!
    To add to Hans' pivot table suggestion, you could add the following macro into VBA for the worksheet that the pivot table is located. This way every time you move into the pivot table worksheet it will automatically refresh the pivot table for you. Likewise, I changed the pivot table to read all 65536 lines of the data worksheet (you could "hide" the blanks, which is an option in the pivot tables). With the pivot table reading the entire length of the data worksheet, you can continuously add data to the database, and then when you go to the pivot table worksheet to view the summarization, it will automatically update for you. I've attached a modified version of Hans' suggestion... of course, I should note that Hans wrote the "refresh pivot tables" macro for me... it works great!!
    Have fun with the pivot tables... they are a great tool!
    Lana

  4. #4
    Star Lounger
    Join Date
    Jun 2006
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Avg, Min, and Max Extraction (2000 SR-1)

    You two are great! Thanks! I love the code for the auto refresh on the pivot table... Always wanted something like that! Two quick questions, when I create the pivot table using the wizard, how do I get it into a horizontal format like yours instead of the default vertical format which is much more difficult to read and compare numbers?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Avg, Min, and Max Extraction (2000 SR-1)

    To get the horizontal layout, drag the gray "Gegevens" (Data) button to the right, to Total, and drop it there.

  6. #6
    Star Lounger
    Join Date
    Jun 2006
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Avg, Min, and Max Extraction (2000 SR-1)

    I figured it was something simple, just didn't know how to do it... Thanks! I've got a calculation error though in the pivot table. Well, maybe not an error, so to speak, but it's not calculating the correct total for the Wait Percentage. The grand total should be 55/64 which is 86%. It is showing 84%. The average, min, and max all check out right!! Thanks for the help.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Avg, Min, and Max Extraction (2000 SR-1)

    To be honest, I have no idea why Excel computes an incorrect overal percentage. The numbers 55 and 64 are displayed correctly. Unfortunately, the numbers a pivot table are not the results of formulas, so we cannot check what causes this.

  8. #8
    Star Lounger
    Join Date
    Jun 2006
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Avg, Min, and Max Extraction (2000 SR-1)

    The percentage is a calculated field. All the individual time block percentages are correct, just the grand total is off. Oh well, not as concerned with the grand total as I am the breakdowns...

    Anyhow, can you take a look at one last thing for me. You've been very helpful, and it's nice to have someone that knows more than me to ask questions to!

    In this attachment, the code creates a static use of the now() function for the start time when a value is entered in column E and then a stop time when a value is entered in column H. This results in their "wait" time when subtracting the two. What I would like to have is something along these lines that would show the wait time as soon as a value is entered in column E and would refresh (something like every 30 seconds) until a value is placed in column H and then would show a final wait time. Basically I want to know how long someone has been waiting while they are still waiting...

  9. #9
    Star Lounger
    Join Date
    Jun 2006
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Avg, Min, and Max Extraction (2000 SR-1)

    Okay, I found a way to get the wait time to display a running total. I used the now() function in the end time. This is then later replaced with the static time from the VB code, so it doesn't really matter what's in there. Anyhow, the wait time shows the wait time for someone while they are still waiting. The only thing I need to do is figure out a way to get the page to refresh automatically (I'm thinking every 5 or 10 seconds would be better than 30 seconds).

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Avg, Min, and Max Extraction (2000 SR-1)

    You could put a formula in column K when a pager is entered in column E:

    ...
    If Target.Column = 5 Then
    Cells(Target.Row, "J") = Now()
    Cells(Target.Row, "K").Formula = "=Now()"
    End If
    ...

    If column N displays the wait time instead of "Timer Started", pressing F9 will update the time.

    It would be possible to write code that recalculates every n seconds, but I would not recommend doing it - it has nasty effects if the user is entering data when the code fires.

  11. #11
    Star Lounger
    Join Date
    Jun 2006
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Avg, Min, and Max Extraction (2000 SR-1)

    Thanks for all of your help! This sheet is a much improved one!!

Posting Permissions

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