Results 1 to 10 of 10
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    The Land of Pleasant Living, Maryland, USA
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Help with Simple(?) Formula

    I am trying to keep track of the MByte download (usage) from my web site to insure that I don't exceed the maximum allowable per month. I can go to the site "stats" at any time during the month and see the current usage for the month.

    I need help from a formula guru with this problem:

    I'm trying to estimate the monthly usage from three facts: the current date and time and the usage to that point.

    I enter the current [DATE] and [TIME] in [A1] and [B1] and the usage so far for the month in [C1].

    Using that data, I would like to calculate the estimated usage for the month in [D1].

    Now that should not be too difficult (RIGHT!). To make it easier, you can assume all months have 30 days (that's close enough for the accuracy I seek).

    Thanks, in advance - sorry I can't award any prizes.

  2. #2
    Star Lounger
    Join Date
    Jan 2001
    Location
    Bloomington, Indiana, USA
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with Simple(?) Formula

    I don't know if I followed what you were asking, but you might try =30*(C3/(DAY(A3))). The date was in cell A3 formatted as a date, I ignored the time (since it was safe to assume 30 days in each month I thought this detail would add a level of complexity you didn't need), the usage in cell C3, and the cell where the formula is formatted as a number.

    Hope it helps...

    Kathy

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Help with Simple(?) Formula

    If the number of days per monthg is not critical, then time is unlikely to be an issue.

    How about the following

    A1 = Date
    B1 = Monthly Usage to date
    c1 = =(B2/DAY(A2))*30

    This works by establishing the average usage per day and multiplying by 30 for the monthly figure. It could of course be enhanced to use the actual no of days in the given month.

    Does that suffice ?

  4. #4
    Star Lounger
    Join Date
    Jan 2001
    Location
    The Land of Pleasant Living, Maryland, USA
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with Simple(?) Formula

    Thanks but perhaps I didn't make myself clear - the time IS important. We are over our limit for monthly MBytes and I am checking back a few times a day to check our status.

    Also, as an educational tip, I'd like to understand the process.

    Peace,
    Mike

  5. #5
    Star Lounger
    Join Date
    Jan 2001
    Location
    The Land of Pleasant Living, Maryland, USA
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with Simple(?) Formula

    Kathy - Thanks for the reply but time is important. Pls see my reply to Andrew.

    Peace,
    Mike

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with Simple(?) Formula

    First, you need to put the current date AND time into a single cell. You can do this by putting the formula =Now() in cell A1, or you could type it in like this: 2/16/2001 5:05pm. Then put the current usage in cell B1. The following formula will calculate the usage at the end of the month if the average for the rest of the month remains the same:

    <pre>=(B1/(A1-DATE(YEAR(A1),MONTH(A1),1)))*(DAY(DATE(YEAR(A1),MO NTH(A1)+1,1)-1))
    </pre>

    Legare Coleman

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Help with Simple(?) Formula

    Using the same approach as Legare the following formula should get the same result. It uses a rate per minute and projects on that basis.

    =(B2/(((DAY(A2-1))*24*60)+(HOUR(A2)*60)+MINUTE(A2)))*(30*24*60)

  8. #8
    Star Lounger
    Join Date
    Jan 2001
    Location
    The Land of Pleasant Living, Maryland, USA
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with Simple(?) Formula

    EXCELLENT! That's just what I needed.

    Thanks.
    Mike

  9. #9
    Star Lounger
    Join Date
    Jan 2001
    Location
    The Land of Pleasant Living, Maryland, USA
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with Simple(?) Formula

    Thanks a bunch. Now I understand what I was missing.

    Peace,
    Mike

  10. #10
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Help with Simple(?) Formula

    That formula should have read
    =(B2/(((DAY(A2)-1)*24*60)+(HOUR(A2)*60)+(MINUTE(A2))))*(DAY(EOMONT H(A2,0))*24*60), which takes into account the correct number of days per month i.e. Feb = 28.

    Sorry about that

    Andrew

Posting Permissions

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