1. 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. 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. Re: Help with Simple(?) Formula

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

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. 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. Re: Help with Simple(?) Formula

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

Peace,
Mike

6. 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>

7. 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. Re: Help with Simple(?) Formula

EXCELLENT! That's just what I needed.

Thanks.
Mike

9. Re: Help with Simple(?) Formula

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

Peace,
Mike