Value Prediction

[Corey]

I have a sheet with statistical data values in range(C9:AM14)
With each row the values will generally descrease in value
Each row has a Date placed in cells B9:B14 to indicate when the data was collected in that row
Not all cells require values in them, so i need to ignore empty cells if there are some

I have a value in AD8 which is the LOWEST value required

What i want to do is have a prediction formula done, so that based on the VALUES in C9:AM14 i get a
Date Value when at least 20% of the values will decrease to the AD8 value


Sheets is like :

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
A B C D E F G H I J ---
AD ----AM AN AO AP
1
-
8
0.5
9 1/1/05 10 10 10 10 10 10 10 10 10 10 10
10 10 10
10 1/6/05 9 9 9 9 9 9 9 9 9 9
9 9 9 9
11 1/1/06 7.5 7.5 7.5 7.5 7.5 7.5 7.5 7.5 7.5 7.5 7.5
7.5 7.5 7.5
12 1/6/06 6.1 6.1 6.1 6.1 6.1 6.1 6.1 6.1 6.1 6.1 6.1
6.1 6.1 6.1
13 1/1/07 5.5 5.5 5.5 5.5 5.5 5.5 5.5 5.5 5.5 5.5 5.5
5.5 5.5 5.5
14 1/6/07 4.2 4.2 4.2 4.2 4.2 4.2 4.2 4.2 4.2 4.2 4.2
4.2 4.2 4.2
-

I want a formula to say give me a date when 20% or greater of the values will be less than or equal
to AD8(0.5)

I got no idea how to get this from the data.

Corey....

 

[Joel]

You can do it with worksheet functions. It is a little complicated but I can
walk you through the steps. It can also be done with a macro if necessary.

1) first you need to calculate the slope using the slope function for each
column

Put this formula in cell C15 and copy it across row 15

=SLOPE(C9:C14,$A9:$A14)

C9:C14 are your values and $9:$14 are the dates.

2) Now extend your dates down column A one day at a time. Put in Cell A16
1/7/07 and in cell A17 1/8/07. Now select A16 and A17 and fill down column A
by putting mouse over square box and bottom right hand corner of selected
area and pulling the box down column A. go down as far as you need.

3) Now put the formular below in cell C16.

=C$14+(C$15*($A16-$A$14))

Using the slope in row 14 this predicts the data based on the dates in
column A and the calculated slope. Copy C16 from column C to column AM and
down the number of rows you put the date in column A.

4) Now all you need to do is count the number of cells less than in AD8.

Put the following formula in cell AN:16 and copy down the number of rows you
have the date in column A.

=COUNTIF(B16:AN16,"<="&AD$8)/COUNTA(B16:AN16)

The formula will calculate the % of cells less than the values in AD8.

5) If you didn't go far enough in dates copy the the rows futher down the
worksheet. Make sure the date column gets extended usingg the technique in
step 2 above.

 

[Corey]

Joel,
Thnak you for the reply ans detailed explaination.
It is appreciated very much.
I followed exactly my interpretation of what you said, but the formula's seem to give me an error.

A 'Divide by Zero error'

Corey....
You can do it with worksheet functions. It is a little complicated but I can
walk you through the steps. It can also be done with a macro if necessary.

1) first you need to calculate the slope using the slope function for each
column

Put this formula in cell C15 and copy it across row 15

=SLOPE(C9:C14,$A9:$A14)

C9:C14 are your values and $9:$14 are the dates.

2) Now extend your dates down column A one day at a time. Put in Cell A16
1/7/07 and in cell A17 1/8/07. Now select A16 and A17 and fill down column A
by putting mouse over square box and bottom right hand corner of selected
area and pulling the box down column A. go down as far as you need.

3) Now put the formular below in cell C16.

=C$14+(C$15*($A16-$A$14))

Using the slope in row 14 this predicts the data based on the dates in
column A and the calculated slope. Copy C16 from column C to column AM and
down the number of rows you put the date in column A.

4) Now all you need to do is count the number of cells less than in AD8.

Put the following formula in cell AN:16 and copy down the number of rows you
have the date in column A.

=COUNTIF(B16:AN16,"<="&AD$8)/COUNTA(B16:AN16)

The formula will calculate the % of cells less than the values in AD8.

5) If you didn't go far enough in dates copy the the rows futher down the
worksheet. Make sure the date column gets extended usingg the technique in
step 2 above.

 

[Corey]

Joel,
Would it be easier in a Macro ?

Joel,
Thnak you for the reply ans detailed explaination.
It is appreciated very much.
I followed exactly my interpretation of what you said, but the formula's seem to give me an error.

A 'Divide by Zero error'

Corey....
You can do it with worksheet functions. It is a little complicated but I can
walk you through the steps. It can also be done with a macro if necessary.

1) first you need to calculate the slope using the slope function for each
column

Put this formula in cell C15 and copy it across row 15

=SLOPE(C9:C14,$A9:$A14)

C9:C14 are your values and $9:$14 are the dates.

2) Now extend your dates down column A one day at a time. Put in Cell A16
1/7/07 and in cell A17 1/8/07. Now select A16 and A17 and fill down column A
by putting mouse over square box and bottom right hand corner of selected
area and pulling the box down column A. go down as far as you need.

3) Now put the formular below in cell C16.

=C$14+(C$15*($A16-$A$14))

Using the slope in row 14 this predicts the data based on the dates in
column A and the calculated slope. Copy C16 from column C to column AM and
down the number of rows you put the date in column A.

4) Now all you need to do is count the number of cells less than in AD8.

Put the following formula in cell AN:16 and copy down the number of rows you
have the date in column A.

=COUNTIF(B16:AN16,"<="&AD$8)/COUNTA(B16:AN16)

The formula will calculate the % of cells less than the values in AD8.

5) If you didn't go far enough in dates copy the the rows futher down the
worksheet. Make sure the date column gets extended usingg the technique in
step 2 above.

 

[Joel]

The countif in step 4 may be the problem. The data for some reason got
shifted over 1 column.

from
=COUNTIF(B16:AN16,"<="&AD$8)/COUNTA(B16:AN16)
to:
=COUNTIF(C16:AM16,"<="&AD$8)/COUNTA(C16:AM16)

Here is my data starting 1/1/2005 in row 9. Column B is empty

a b c d an
1/1/2005 10 10
1/6/2005 9 9
1/1/2006 7.5 7.5
1/6/2006 6.1 6.1
1/1/2007 5.5 5.5
1/6/2007 4.2 4.2
slope -0.006386771 -0.006386771
1/7/2007 4.193613229 4.193613229 0
1/8/2007 4.187226457 4.187226457 0
1/9/2007 4.180839686 4.180839686 0
1/10/2007 4.174452914 4.174452914 0
1/11/2007 4.168066143 4.168066143 0
1/12/2007 4.161679371 4.161679371 0
1/13/2007 4.1552926 4.1552926 0
1/14/2007 4.148905829 4.148905829 0
1/15/2007 4.142519057 4.142519057 0
1/16/2007 4.136132286 4.136132286 0
1/17/2007 4.129745514 4.129745514 0
1/18/2007 4.123358743 4.123358743 0
1/19/2007 4.116971971 4.116971971 0
1/20/2007 4.1105852 4.1105852 0
1/21/2007 4.104198429 4.104198429 0

Here is the formulas. The dates are in number format (ignore 3XXXX in
column A)
a b c d an
38353 10 10
38358 9 9
38718 7.5 7.5
38723 6.1 6.1
39083 5.5 5.5
39088 4.2 4.2
slope =SLOPE(C9:C14,$A9:$A14) =SLOPE(D914,$A9:$A14)
39089 =C$14+(C$15*($A16-$A$14)) =D$14+(D$15*($A16-$A$14)) =COUNTIF
C16:AM16,"<="&AD$8)/COUNTA(C16:AM16)
39090 =C$14+(C$15*($A17-$A$14)) =D$14+(D$15*($A17-$A$14)) =COUNTIF(C17:AM17,"<="&AD$8)/COUNTA(C17:AM17)
39091 =C$14+(C$15*($A18-$A$14)) =D$14+(D$15*($A18-$A$14)) =COUNTIF(C18:AM18,"<="&AD$8)/COUNTA(C18:AM18)
39092 =C$14+(C$15*($A19-$A$14)) =D$14+(D$15*($A19-$A$14)) =COUNTIF(C19:AM19,"<="&AD$8)/COUNTA(C19:AM19)
39093 =C$14+(C$15*($A20-$A$14)) =D$14+(D$15*($A20-$A$14)) =COUNTIF(C20:AM20,"<="&AD$8)/COUNTA(C20:AM20)
39094 =C$14+(C$15*($A21-$A$14)) =D$14+(D$15*($A21-$A$14)) =COUNTIF(C21:AM21,"<="&AD$8)/COUNTA(C21:AM21)
39095 =C$14+(C$15*($A22-$A$14)) =D$14+(D$15*($A22-$A$14)) =COUNTIF(C22:AM22,"<="&AD$8)/COUNTA(C22:AM22)
39096 =C$14+(C$15*($A23-$A$14)) =D$14+(D$15*($A23-$A$14)) =COUNTIF(C23:AM23,"<="&AD$8)/COUNTA(C23:AM23)
39097 =C$14+(C$15*($A24-$A$14)) =D$14+(D$15*($A24-$A$14)) =COUNTIF(C24:AM24,"<="&AD$8)/COUNTA(C24:AM24)
39098 =C$14+(C$15*($A25-$A$14)) =D$14+(D$15*($A25-$A$14)) =COUNTIF(C25:AM25,"<="&AD$8)/COUNTA(C25:AM25)
39099 =C$14+(C$15*($A26-$A$14)) =D$14+(D$15*($A26-$A$14)) =COUNTIF(C26:AM26,"<="&AD$8)/COUNTA(C26:AM26)
39100 =C$14+(C$15*($A27-$A$14)) =D$14+(D$15*($A27-$A$14)) =COUNTIF(C27:AM27,"<="&AD$8)/COUNTA(C27:AM27)
39101 =C$14+(C$15*($A28-$A$14)) =D$14+(D$15*($A28-$A$14)) =COUNTIF(C28:AM28,"<="&AD$8)/COUNTA(C28:AM28)
39102 =C$14+(C$15*($A29-$A$14)) =D$14+(D$15*($A29-$A$14)) =COUNTIF(C29:AM29,"<="&AD$8)/COUNTA(C29:AM29)
39103 =C$14+(C$15*($A30-$A$14)) =D$14+(D$15*($A30-$A$14)) =COUNTIF(C30:AM30,"<="&AD$8)/COUNTA(C30:AM30)

 

[Joel]

You can get more accurate answers by using the intecept function along with
the slope. My original code used the point on row 14 to make the
predication. The slope on row 15 is the median line drawn in the middle of
the point in rows 9 to 14 and not directly through the value in row 14.
Using the Intecept function makes the slope and the intecept use the same
line going through the middle of all the points.

Also the dates in column A don't have to be every day, they can be every 6
months. See my new results below.

1) Add new row by clicking on row 16 and insert new row
2) Put the following formula in cell C16
=INTERCEPT(C9:C14,$A9:$A14)
3) Copy C16 across row 16 from column D to column AM
4) Put this new formula in cell C17 which is really y=mx+b where m is the
slope from row 15 and b is the intecept from row 17. X is the date from
column A
=(C$15*$A17)+C$16
5) copy C 17 to column AM and down the number of rows.


1/1/2005 10 10 10
1/6/2005 9 9 9
1/1/2006 7.5 7.5 7.5
1/6/2006 6.1 6.1 6.1
1/1/2007 5.5 5.5 5.5
1/6/2007 4.2 4.2 4.2
slope -0.006386771 -0.006386771 -0.006386771
intercept 254.3489832 254.3489832 254.3489832
1/1/2008 2.403623783 2.403623783 2.403623783 0
1/6/2008 2.371689926 2.371689926 2.371689926 0
1/1/2009 0.066065439 0.066065439 0.066065439 1
1/6/2009 0.034131582 0.034131582 0.034131582 1
1/1/2010 -2.265106133 -2.265106133 -2.265106133 1
1/6/2010 -2.297039991 -2.297039991 -2.297039991 1
1/1/2011 -4.596277706 -4.596277706 -4.596277706 1
1/6/2011 -4.628211563 -4.628211563 -4.628211563 1
1/1/2012 -6.927449279 -6.927449279 -6.927449279 1
1/6/2012 -6.959383136 -6.959383136 -6.959383136 1
1/1/2013 -9.265007623 -9.265007623 -9.265007623 1
1/6/2013 -9.29694148 -9.29694148 -9.29694148 1
1/1/2014 -11.5961792 -11.5961792 -11.5961792 1
1/6/2014 -11.62811305 -11.62811305 -11.62811305 1