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...
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.
Ron
Knowledge is knowing that a tomato and pepper are fruits. Wisdom is knowing whether to put these in a fruit salad
Kindly
[1] Use code tags. Place "[code]" before the first line of code and "[/code"]" after the last line of code. Exclude quotation marks
[2] Mark your post [SOLVED] if it has been answered satisfactorily by editing your original post in advanced mode.
[3] Thank (using the little scale) those that provided useful help; its nice and its very well appreciated
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
Then copy and paste this down to the 8,760th row (I think).=PERCENTILE(OFFSET(A$2,12*(ROW() - 1),,12,), 0.95)
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...
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks