Results 1 to 10 of 10
Thread: Help with Simple(?) Formula

20010216, 12:39 #1
 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.

20010216, 13:05 #2
 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

20010216, 13:20 #3
 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 ?

20010216, 14:29 #4
 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

20010216, 14:31 #5
 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

20010216, 15:07 #6
 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/(A1DATE(YEAR(A1),MONTH(A1),1)))*(DAY(DATE(YEAR(A1),MO NTH(A1)+1,1)1))
</pre>
Legare Coleman

20010216, 15:27 #7
 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(A21))*24*60)+(HOUR(A2)*60)+MINUTE(A2)))*(30*24*60)

20010216, 17:26 #8
 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

20010216, 17:27 #9
 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

20010216, 22:13 #10
 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