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" wrote:

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