Calculating percentiles using individual cells instead of an array.

[mcb123]

So I need to calculate the 95th and 5th percentile for a specific set of cells. However, my data is 10s of thousands of rows long as it is temperatures from every day of the year over a period of 30-100 years. So I want to work out the 95th percentile for the temperature on January 1st of every year.

I know the formula for the percentile function requires an array but my data isn't in this format.

I know this formula is incorrect but in theory, I need something along the lines of:
=percentile(B2;B368;B773,0.95)

Thanks!!

 

[Domenic]

I'm not familiar with the Mac version of Excel, but hopefully this works for you...

=PERCENTILE((B2,B368,B773),0.95)

 

[mcb123]

Yes that worked thank you so much!

 

[Domenic]

That's great, glad I could help. Cheers!