HELP!!! Find Percentiles over and over!

fearfour

New Member
Joined
Sep 19, 2006
Messages
14
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'>
Historical NG Percentiles DAILY Oct 2006.xls
ABCDEFGHIJKL
5
69/29/20059/30/200510/3/200510/4/200510/5/200510/6/200510/7/2005*---->andsoon
7100.0%
887.5%
975.0%
1062.5%
1150.0%
1237.5%
1325.0%
1412.5%
150.0%
16
17
1809/29/045.848Thislistgoesonforthreeyearsworthofdata,IbasicallyneedtoselectcellsB18:B268andfindthepercentiles
1909/30/045.860i.e.whatvaluesfallinthesedistributionpercentiles.ThisresultwouldgoincellsC7:C15.
2010/01/045.813
2110/04/045.845Ithenneedtodothesamepricessforeachday.ForexampleD7:D15wouldbethepercentilesforcellsB19:B269
2210/05/045.904WhichisonedaylaterthanwasthecastinthepercentilesforcellsC7:C15.
2310/06/045.939
Sheet1
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

fearfour

New Member
Joined
Sep 19, 2006
Messages
14
I think I can use the PERCENTILE function, but then I have to keep moving the references, etc., and i wanted to find soemthnig that was more automated. Any ideas???
 

eliW

Well-known Member
Joined
Mar 2, 2002
Messages
1,934
Hi

It is a good idea to setup your page horizontaly instead of vertically. This way you can copy your formula down so that the range will change in every row in one day.

See the example.

Eli
Book1
ABCDEFG
2DATEDATADATE100%80%60%
301/01/200410017/01/20041002010
402/01/20042018/01/200430176
503/01/20043019/01/200430104
604/01/20043020/01/200430104
705/01/20041021/01/20041763
806/01/2004222/01/20041743
907/01/20041023/01/20041743
1008/01/2004624/01/20041743
1109/01/200417
1210/01/20044
1311/01/20044
1412/01/20043
1513/01/20043
1614/01/20042
1715/01/20043
1816/01/20043
1917/01/20043
2018/01/20043
2119/01/20043
2220/01/20041
2321/01/20041
2422/01/20041
2523/01/20044
data
 

Forum statistics

Threads
1,136,272
Messages
5,674,757
Members
419,525
Latest member
helensesc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top