Hi everyone, this problem is a bit difficult to describe. Basically, I need to find the percentiles of a set of data which will change each day. The way that the data will change is that the data analyzed will move one day for each day of the year, so in other words I need to find the percentiles for the future price of natural gas as they occured for each day in the past year....so as each new day starts the conecpt of "the past year" shifts by one day. I am going to have to rely pretty heavily on the excel sheet I am including in this post. Now, I have tried using the percentile function with little success.....but to be perfectly honest I never did that well in stats class....I was a lot better in Chemistry!
Thank you so much in advance for your help, I have to hand this project off to a user who is not very excel literate and it will save us a lot of time!
Okay here is the sheet as it appears:</Script></HEAD><BODY BGCOLOR=#E0F4EA><CENTER><FONT COLOR=#339966 SIZE=5>[HtmlMaker 2.42]</FONT></CENTER><HR><SPAN id='ForSubmit'>
Thank you so much in advance for your help, I have to hand this project off to a user who is not very excel literate and it will save us a lot of time!
Okay here is the sheet as it appears:</Script></HEAD><BODY BGCOLOR=#E0F4EA><CENTER><FONT COLOR=#339966 SIZE=5>[HtmlMaker 2.42]</FONT></CENTER><HR><SPAN id='ForSubmit'>
| Historical NG Percentiles DAILY Oct 2006.xls | ||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | |||
| 5 | ||||||||||||||
| 6 | 9/29/2005 | 9/30/2005 | 10/3/2005 | 10/4/2005 | 10/5/2005 | 10/6/2005 | 10/7/2005 | *---->andsoon | ||||||
| 7 | 100.0% | |||||||||||||
| 8 | 87.5% | |||||||||||||
| 9 | 75.0% | |||||||||||||
| 10 | 62.5% | |||||||||||||
| 11 | 50.0% | |||||||||||||
| 12 | 37.5% | |||||||||||||
| 13 | 25.0% | |||||||||||||
| 14 | 12.5% | |||||||||||||
| 15 | 0.0% | |||||||||||||
| 16 | ||||||||||||||
| 17 | ||||||||||||||
| 18 | 09/29/04 | 5.848 | Thislistgoesonforthreeyearsworthofdata,IbasicallyneedtoselectcellsB18:B268andfindthepercentiles | |||||||||||
| 19 | 09/30/04 | 5.860 | i.e.whatvaluesfallinthesedistributionpercentiles.ThisresultwouldgoincellsC7:C15. | |||||||||||
| 20 | 10/01/04 | 5.813 | ||||||||||||
| 21 | 10/04/04 | 5.845 | Ithenneedtodothesamepricessforeachday.ForexampleD7:D15wouldbethepercentilesforcellsB19:B269 | |||||||||||
| 22 | 10/05/04 | 5.904 | WhichisonedaylaterthanwasthecastinthepercentilesforcellsC7:C15. | |||||||||||
| 23 | 10/06/04 | 5.939 | ||||||||||||
Sheet1 | ||||||||||||||
