Statistics Plus Minus Enrollment

[pkaraffa]

I think that I am making this more complicated than this is but here it
goes. At work we were given 3 years of enrollment data. I know that the
more data we have the better we are but all we were given at work is 3
years of data to work with! Our task is to make a prediction on the 4th
year of data. What I need is a positive or negative ex. 16 or -16. I an
using =FORECAST(2007,B22,$B$1:$D$1) this equals 22.66666667. I also
tried =TREND(B22,$B$1:$D$1,2007) which gave me 22.66666667. This
tells me 22.66666667 but it does not give me a minus number. IAnother
approach I took was =(C2-B2)+(D2-C2) this gives me a -16. Thisis the
type value that I am looking for. To find the range I used
=MAX(B22)-MIN(B22) and to see if the data was trending up down or
stable (Harlan's formula)
=CHOOSE(2+SIGN(INDEX(LINEST(B22),1)),"Down","Stable","Up"). My
question is what is the right approch to get the plus or minus figure?
If I use trend or forcast it does not tell me +Number or -number. Is
using =(C2-B2)+(D2-C2) this gives me a -16 is this the right approach?
Can someone please guide me! Thanks in advance.

2004 2005 2006
48 36 32

 

[Guest]

If we put your data in X1 thru Y3:

1 48
2 36
3 32

you can see that the data exactly matches the formula

=24*(1+1/X1). So for X4=4 (that is year 2007) the result is 30.

 

[pkaraffa]

=24*(1+1/X1). What does the 24 signify? And are you saying that
Through all 3 years the enrollment will be -2? (2006(32)-2007(30)which
will give us -2.

 

[Guest]

No.

in 2004 x1 is 1 and =24*(1+1/X1) yields 48 the value you posted
in 2005 x2 is 2 and =24*(1+1/X2) yields 36 the value you posted
in 2006 x3 is 3 and =24*(1+1/X3) yields 32 the value you posted
in 2007 x4 is 4 and =24*(1+1/X4) yields 30 the value I project
in 2008 x5 is 5 and =24*(1+1/X5) yields 28.8 the value I project


To make the formula simple I used 1,2,3,4,5 rather than
2004,2005,2006,2007,2008

We were were lucky that the three data points you posted exactly fit a
simple formula.

 

[pkaraffa]

Thanks for your help thus far. How would your formula work for a set of
data points like 71, 70, 76?

 

[Guest]

I'll look at them tomorrow. At first blush a parabola (quadratic) may be best.