Help needed (idiot onboard)

A

Anonymous Coward

Hi All,

I hope someone can help me with this; I've bashed a good-sized dent
in my desktop and would like to stop before the desk breaks
altogether.

I have a spreadsheet that I track my server hard drive free space on.
It does some basic averaging on consumption and the like. What I
would really like to be able to do is forecast when I am going to run
out of space.

I know the basics of what I want to do and I am sure I have all of the
parts, I just don't know how to cobble them together in a cell and
have it spit out my answer.

Here is what I have:

- Column A = today's date
- Column B = free bytes on HD
- Column C = byte change from yesterday (Btoday-Byesterday)
- A standalone cell that averages Column C (average daily consumption)

So; it should be a relatively simple FORECAST or TREND to tell me that
in x-number of days, I will reach x-number of bytes available.

If some kind soul is willing to help me along on this path, the amount
of gratitude poured forth would be excessive.
 
B

Bernard Liengme

Hi,
We have to assume the data is linear ( a plot of date vs free sapce gives a
straight line)
So y=mx +b (something about your words makes you sound British, if so read
y=mx +c) - the thing you learned in school. If you knew m and b then for to
compute an X-value for a specified Y-value you need X= (Y-B)/M

You can get M with SLOPE and B with INTERCEPT. so if you dates are in
A1:A100 ans space values in B1:B100 then with the required space value in F1
use
=(F1-INTERCEPT(B1:B100,A1:A100)/SLOPE(B1:B100,A1:A100)

If the data is non linear you will need to use LINEST (see
www.stfx.ca/people/bliengme/ExcelTips ). Alternatiively, make a chart, add a
trendline and use the exend feature of trendline to see where it get to the
specified x-value.
Best \wishes
Bernard
 
T

Tom Ogilvy

If you are assuming a linear progression, then

y = ax + b

use slope and intercept with your data to get a and b respectively.

set y equal to zero and solve for x


0 = ax + b
ax = -b
x = -b/a


Assume you have 200 gigabytes and the slope comes out to be -1.3 GB per day.
(a = -1.3GB)

b = 200 (for the date you installed the drive -

x = -200/-1.3 = 153.8 days
 
G

George Raymond

Hi
I know how you feel - I've been there.
Sometimes we try to solve a simple thing with complicated
solutions. Dont look for FORECASTs or TRENDs. Give you an
example:
If you have 100 apples and you eat an average of 2 a day
then devide the available apples by the daily consumption
i.e. 100\2 = 50 days before you ran out apples!
In your case
Devide the free bytes on HD (Column B) by the result of
the standalone cell with the average daily consumption.
I would use the "\" instead of the "/" devider to get an
integer answer.

Good luck .

George
 
W

W. Smith

Thanks for the help with this! Once I get my data tweaked around a
little, it should fit the model quite nicely.

Have a safe and happy Christmas...



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
 
Top