How to get percentile from a selected data range

[climate]

Hi all,

Im trying to get a 95 percentile from a selected range of data. My problem is, How to get the range of data that match the year and month with their value (since I want a monthly percentile) and then get the monthly 95 percentile value.

I only get one monthly 95 percentile value from an hourly data range for that particular month for 30 years. How can I do so to get the percentile automatic for every annual months.

Attached is the sample data for 1 year (12months x 31 days x 24 hour data each year)only since the data size is to big.

Any ideas and help is appreciated.Thanks in advance!

[Cheeky Charlie]

Er, I'm struggling a little with your calculation, but you may find using a pivot table to summarise your data helpful.

What result would you be looking for based on your example?

[climate]

I wanna get the 95 percentile from the "data" sheet. In the "data" sheet, there are 744 (31 days x 24 hours) data values for each month whereby I want to get the monthly 95 percentile from every 744 hourly value for every year from 1980-2009. It take time to do by manually using the percentile function since my data size is very big.

Any idea of doing this? Thanks!

[Cheeky Charlie]

what result would you be looking for based on your example?

1234567890

[estige]

Hi, here is a suggestion using indirect.

[Cheeky Charlie]

As indirect is volatile I would not recommend using indirect on such a large data source.

In this example indirect could easily be replaced with index - but I am still unsure what the OP is looking for.

[estige]

OK, but out of curiosity (if I have interpreted the OP correctly): I can't think of a way to replace indirect with index as "input" to the percentile-function. Can this be done, or were you thinking of avoiding the percentile-function too, for example by sorting the data within each month so the 95th percentile within each month can be looked up directly?

[Cheeky Charlie]

How about:
=PERCENTILE(INDEX(E:E,2+(I3-1+(H3-1980)*12)*744):INDEX(E:E,1+(I3+12*(H3-1980))*744),0.95)
?

[estige]

Thanks CC, works prefectly. This is a keeper for me!!

[climate]

Thanks CC and estige. Thanks for your kindly help! This is really helpful.

Have a great day! cheers!

[estige]

Thanks, climate.
One last thing - I appologize for using the function percentile.inc in my solution - in office 2007 this should be changed to percentile. (As far as I can see this function wasn't introduced in office 2007 as I thought, but in 2010.)

[climate]

sure.. cheers! :-)

[Cheeky Charlie]

(from PM)

The function formula as below:
=PERCENTILE(INDEX(E:E,2+(I3-1+(H3-1980)*12)*744):INDEX(E:E,1+(I3+12*(H3-1980))*744),0.95)

Can you explain the highlighted part of this equation, what does it stand for. Sorry for troubling you. Thanks a lot! cheers!

It really follows the logic outlined in your first post - first part:
In column E:
To get the start of your range:
multiply (the month number + (12 * (the number of years after 1980)) by 744
so (12 * (the number of years after 1980)) simply converts the years to months for the right offset
we multiply by 744 because 744 = 31*24, as you pointed out
Then we add a static offset of 1 because that is which row your data starts on (after you accomodate the first month number)

To get the end of the range (i.e. the second highlighted section) we apply logic so similar I can't believe you want me to type it out...

Sure you don't mind me posting an excerpt from your PM, climate. On the forum we like all answers to be shared with everyone, answering via PM is a bit of a waste.

PS I highly recommend the evalute formula tool (I don't know where to find it in XL07) it's one of Excel's best-kept secrets - I use it all the time (and have created a shortcut button to it right next to my formula bar, why we become obvious when you use it).

[climate]

Sure, it's good to share with everyone. Just that, previously, I thought cannot submit this kind of msg here.

Thanks CC!