Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Nov 2011
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Computing Concurent Usage from Log File with Multiple Criteria

    I am trying to analyze date from a software usage log file. The key columns contain a username, a start time and an end time. Complicating the matter is that the start and end times while recorded to the minute, must be rounded to one hour intervals and analyzed based upon those times.
    I use Floor and Ceiling to round the times so every occurrence gets counted for a minimum of 1 hour.
    However, there are times when one user has multiple rows within these periods, due to the rounding and also since sometimes a user opens multiple sessions in the programs being logged.
    So I need to count the number of concurrent users (counting any one user only once) and determine the maximum number of users at any given time.
    Here is my data:
    • The data starts in Row 3.
    • UserName is in column B and I am using a named range UserName to define the range.
    • My StartTime is in Column G
    • My Stop Time is in Column H

    This is the formula I cam up with, but am finding it hard to know if I am correct. I am more of an Excel hacker than a Guru and would never have even tried SUMPRODUCT had I not found examples online.

    =SUMPRODUCT(--(G5>=G$3:G$1931),--(G5<H$3:H$1931),--(UserName=B3))

    Can anyone chime in an tell me, is this correct?
    I have determined that =SUMPRODUCT(--(UserName=B3)) returns the number of total count of all records in the range where the username is equal to the username in cell B3.
    And can anyone think of how I might plot this with only 1 row per user?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Could you upload an example file with some "problem data" and elaborate on what the formula is supposed to obtain exactly and what you want to do?

    Steve

  3. #3
    New Lounger
    Join Date
    Nov 2011
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This file has one day of usage. There are four time columns: Two are actual start and stop date and time, two are the rounded to whole hour times.
    I need to see concurrent usages per hour where each users usage is only counted as 1, even if they have more than one in any particular time. My formulas in L, M and P are supposed to be some type of total. Their headers have comments explaining more. If I can get each count column - L and M - to sum the total number of concurrent usages taking into account the UserName I can then use a max function to see the maximum usage and also see when it occures and who was using the softwere when it occured.
    The first 6 rows show 1 hour usages for 12 AM to 1 AM (3 each) and 1 AM to 2 AM (3 each).
    The next 3 rows show two 1 hour usages from 2 AM to 3AM on then a 2 hour usage from 2 AM to 4 AM.
    Then the next 3 rows show two 1 hour usages from 3 AM to 4AM on then a 2 hour usage from 3 AM to 5 AM.
    All of these are a single user. This means that in both columns L and M, I should see a 1. But as you can see, I'm getting a 1 in column L but I get a bunch of 3's and 4's in column M.
    Attached Files Attached Files

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I am confused. How do you count the ones where they start multiple times in the same hour, but end in different hours? Is it the end time that defines the uniqueness or the start time?

    Steve
    PS what values are you expecting for the user1 - user6 in the sample data. Depending on the criteria I can chose "duplicates" in several ways...
    Last edited by sdckapr; 2013-10-17 at 15:26.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I think the dataset is too complex to do with those array type functions. I think it would work if the start and stop were always 1 hour, but with some of the time-frames for a person being 5 hours, you must take into account each hour individually. The way I would approach it is in Sheet 2. It uses formulas to determine if a user is active in each of the hour periods and I used conditional formatting to display a "pseudo-chart" of the results. The sum of the periods is above the user names. The range can be extended to add more users and more time if the min/max is different. The green date-times are in the range, the red ones outside

    I hid the values in the table (via number formatting), The names without time are crossed out via cond formatting

    I hope this is useful to you. I think it gives you the values you want and also gives a visual reference as well. It can be modified if desired...

    Steve
    PS I modified the file to add the total number of concurrent users at each hour period...
    Attached Files Attached Files
    Last edited by sdckapr; 2013-10-17 at 19:19.

  6. #6
    New Lounger
    Join Date
    Nov 2011
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Unfortunately, having to manually manipulate the data and sheets any time I need or want to analyze these reports is less than ideal. I was really hoping for adding a column or two of formulas being my only task to get my information.
    However, what you supplied looks like something I can probably use.
    As a test, I copied your new tab and modified it to look at 5 minute intervals and report usage using the "real time" columns (C & D) and that worked. I had to rework a few formulas to use the expanded times and other range changes.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    It was setup to not have to manually manipulate the data at all. It only summarizes what is in the dataset. As I mentioned, I don't think any array formula could get what you want. I was playing with user6 who had only 7 rows. Only 1 mattered the one where he was active for 5 hours. The others all intersected...

    I am glad you were able to modify it to look at other intervals. I hadn't thought to create that as a simple input, since you only mentioned doing it in hour-buckets. The min/max is read from the data, but you could include a min/max for extraction and a bucket-interval as cells and then base the calcs on that. If you need any additonal explanations on what I sent or need additional help, post back and we can try to help.

    Steve

Posting Permissions

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