How to quickly apply a formula to a group of cells down a column

**dnrose19** · 09-02-2011, 03:25 PM

Hello,
I have a long column of data that I'd like to calculate the 95th percentile for sets of data within that column (data is for every 5 minutes in a year, and I'd like to calculate the 95th percentile per hour). Is there a way to calculate the 95th percentile for every 12 rows, but do it quickly for the entire 105121 rows? Thus the result would be 1 cell for each of those percentile results, and all of these results in the same column? Hopefully I explained this clearly...

**ron2k_1** · 09-02-2011, 03:41 PM

Assuming that you already have your formula to calculate the 95th percentile, and you already have it on your first cell; all you need to do is to select the range of 12 cells (cell with the 95 percentile formula being on the 12th selected row), take your cursor to the 12th cell's bottom right corner and dragdown as needed. Eg. if your data starts on row 1, the percentile calculations are on Col C, Select C1:C12, and drag down as needed. Or Select C1:C12, Copy, select C1:C105121 and click paste.

**Mav3r1ck95** · 09-02-2011, 03:52 PM

I'm assuming that your column with the data has a header here, since (12*24*365) + 1 = 105121, and that it is in Column A.

In any other column, select the cell in the first row and type in

Please Login or Register  to view this content.

Then copy and paste this down to the 8,760th row (I think).

In row 1, this creates the array A2:A13, and finds the 95% percentile of this range.
In row 2, this creates the array A14:A25, and finds the 95% percentile of this range.

and so on...