Calculating percentiles using individual cells instead of an array.

mcb123

New Member
Joined
Apr 7, 2020
Messages
2
Office Version
  1. 365
Platform
  1. MacOS
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!!
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,453
I'm not familiar with the Mac version of Excel, but hopefully this works for you...

VBA Code:
=PERCENTILE((B2,B368,B773),0.95)
 

mcb123

New Member
Joined
Apr 7, 2020
Messages
2
Office Version
  1. 365
Platform
  1. MacOS
Yes that worked thank you so much!
 

Watch MrExcel Video

Forum statistics

Threads
1,126,952
Messages
5,621,797
Members
415,856
Latest member
jimb2k

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