1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Please help with my formula in Excel

Discussion in 'Business Applications' started by trex2002, Jul 1, 2003.

Thread Status:
Not open for further replies.
Advertisement
  1. trex2002

    trex2002 Thread Starter

    Joined:
    Jul 1, 2003
    Messages:
    2
    Can anyone help me with the following problem,

    I have an Excel 2000 spreadsheet with 7000 rows showing when a user connects or disconnects. The first column shows date in the following format “dd/mm/yyyy” and the second column shows when the user connected in the format “hh:mm” and then the third column shows when they disconnect in the same format. Finally the fourth column shows the length they were connected I need to create a time line for a complete month in intervals of every minute to show how many users are connected at any point in time over that month. . I am going to create the time line across the top of the page but I am getting stuck on the formula to compare the information and report back a valid number. If any one could help me with this It would be greatly appreciated.

    Date Start time End time Length

    22/04/2003 13:59 14:18 00:19
    22/04/2003 14:01 14:18 00:16
    28/04/2003 12:07 13:07 01:00
    28/04/2003 12:56 13:45 00:49
    28/04/2003 12:59 13:45 00:46
    28/04/2003 12:59 13:45 00:46
     
  2. deej

    deej

    Joined:
    Jun 11, 2003
    Messages:
    152
    Have I got this right - you want to count how many users are connected for every minute of every day of each month? That's 1440 minutes per day - 44640 in a 31 day month?

    Deej, UK
     
  3. trex2002

    trex2002 Thread Starter

    Joined:
    Jul 1, 2003
    Messages:
    2
    This is correct, I tried doing it in excel but had problems with limitations of only 250 columns, I then split this over 4 sheets covering a couple of hours for each sheet but soon as I dragged the formula across half of the first sheet the file got to about 130Mb. I was unable to open or save the document as it had run out of memory. I might have to look at contracting this out to someone who can write VB because I also now need to be able to look at which was the busiest week and where peak time are. Thanks for the response anyway.
     
  4. deej

    deej

    Joined:
    Jun 11, 2003
    Messages:
    152
    Wow! That's a lot of data to keep up to date! My figure of 44640 records for a 31 day month represents just 1 user connected during every minute of the day - presumably it could be more.

    I suggest you might be better off using a well designed database - it will be easier to validate data as it is keyed in and you (or someone on your behalf) will be able to create endless queries and reports to do your analysis and calculations for you. As a database programmer I would say that wouldn't I? It might be appropriate to establish a separate table for each month to keep the number of records manageable - all depends on what processing is required.

    Given the number of records (rows) you are planning to store I think you will find that 130Mb is not excessive, so be prepared for that and more ultimately.

    If this is a commercial business application then you might do well to bite the bullet and contact a business software or database specialist local to you, someone who can look at your application and suggest creative solutions that will benefit your business in many ways (not just solve the immediate problem). You never know, there may be an off-the-shelf system just right for you.

    Good luck with this one.

    Deej, UK
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/143567

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice