Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Statistics Plus Minus Enrollment
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,B2:D2,$B$1:$D$1) this equals 22.66666667. I also tried =TREND(B2:D2,$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(B2:D2)-MIN(B2:D2) and to see if the data was trending up down or stable (Harlan's formula) =CHOOSE(2+SIGN(INDEX(LINEST(B2:D2),1)),"Down","Sta ble","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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Statistics Plus Minus Enrollment
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. -- Gary's Student " wrote: 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,B2:D2,$B$1:$D$1) this equals 22.66666667. I also tried =TREND(B2:D2,$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(B2:D2)-MIN(B2:D2) and to see if the data was trending up down or stable (Harlan's formula) =CHOOSE(2+SIGN(INDEX(LINEST(B2:D2),1)),"Down","Sta ble","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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Statistics Plus Minus Enrollment
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. -- Gary''s Student " wrote: =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. wrote: 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,B2:D2,$B$1:$D$1) this equals 22.66666667. I also tried =TREND(B2:D2,$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(B2:D2)-MIN(B2:D2) and to see if the data was trending up down or stable (Harlan's formula) =CHOOSE(2+SIGN(INDEX(LINEST(B2:D2),1)),"Down","Sta ble","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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Statistics Plus Minus Enrollment
Thanks for your help thus far. How would your formula work for a set of
data points like 71, 70, 76? wrote: =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. wrote: 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,B2:D2,$B$1:$D$1) this equals 22.66666667. I also tried =TREND(B2:D2,$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(B2:D2)-MIN(B2:D2) and to see if the data was trending up down or stable (Harlan's formula) =CHOOSE(2+SIGN(INDEX(LINEST(B2:D2),1)),"Down","Sta ble","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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Statistics Plus Minus Enrollment
I'll look at them tomorrow. At first blush a parabola (quadratic) may be best.
-- Gary's Student " wrote: Thanks for your help thus far. How would your formula work for a set of data points like 71, 70, 76? wrote: =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. wrote: 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,B2:D2,$B$1:$D$1) this equals 22.66666667. I also tried =TREND(B2:D2,$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(B2:D2)-MIN(B2:D2) and to see if the data was trending up down or stable (Harlan's formula) =CHOOSE(2+SIGN(INDEX(LINEST(B2:D2),1)),"Down","Sta ble","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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dealing with minus numbers in formulas | Excel Discussion (Misc queries) | |||
Equivalent of Minus in Excel. Also Union, Intersect. | Excel Worksheet Functions | |||
converting minus quantities to plus | New Users to Excel | |||
strip minus signs from right to left | Excel Discussion (Misc queries) | |||
minus formel/fortegn | Excel Discussion (Misc queries) |