Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Value Prediction
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.... |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Value Prediction
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.... |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Value Prediction
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.... "Joel" wrote in message ... 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.... |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Value Prediction
Joel,
Would it be easier in a Macro ? "Corey" wrote in message ... 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.... "Joel" wrote in message ... 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.... |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Value Prediction
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(D9:D14,$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) "Corey" wrote: 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.... "Joel" wrote in message ... 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.... |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Value Prediction
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 "Joel" wrote: 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(D9:D14,$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) "Corey" wrote: 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.... "Joel" wrote in message ... 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.... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Autofill/Autocomplete Prediction PullDown Lists | Excel Discussion (Misc queries) | |||
Rolling Average for Prediction | Excel Discussion (Misc queries) | |||
Vulnerability Prediction Tool | Charts and Charting in Excel | |||
the best prediction formula?URgent! | Excel Worksheet Functions |