Hello, I'm trying to calculate percentiles for different groups in column A using random value data in column B, but ignoring blanks and zeros. I've searched around, but everywhere I've seen shows ranges of data, and the formula can't quite get me what I'm looking for. And while my example only...
Hello!
Hope you are doing great. I have a long list with the following structure:
Name Res 90th P Res
A 110.8 113.2
A 115.6
A 105.8
A 106.2
A 104.9
A 102.2
B 114.1 113.44
B 111.9
B 109.8
B 110.8
C 108.5 108.98
C...
Hey,
I am trying to calculate a percentile of salleries in a table if they've worked between 24 and 60
MonthsWorked
Sallery
22
12124
23
12462
24
12521
24
12523
28
125232
60
13542
62
13800
What I've done so far which actually works:
25% Percentile sallery Monthsworked <24...
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...
Hi,
I am trying to calculate the Median & 75th Percentile for the range in array where the range differs in the length. For example, my data looks like the one given below (my data-set is almost 100,000 rows). Now I am trying to find the median and 75th percentile values of a combined set of 3...
I want to know how to calculate percentile for a large data set. I have to calculate percentiles of 5th,10th,20th,30th,40th,50th,60th,70th,75th,80th,90th and 95th for a known value of data lets say.
1.456
1.234
1.457
1.453
1.789
1.670
1.543
1.234
1.342
1.568
I can do...
Hi,
I am trying to create a macro which will calculate percentile for a set of range in a row but the numbrs of cells change and hence the range needs to be dynamic. I have record the macro:
Sub mrexcel()
Range("A117").Select
ActiveCell.FormulaR1C1 =...
Hello,
I have data with multiple columns. In one column I have months designated 1 - 12 (column C). In another column I have values such as temperature (column P). I am trying to calculate the 85th percentile of only the months may - October (5 - 10). I have tried a nested percentile if and...
Dear experts,
with EXCEL 2010 there is this nice percentile_inc function that gives the k-percentile value of a part of this worksheet.
This works fine - also for a 2-dimensional table, and even the values must not be sorted.
Now, I want to use this function within a VBA program.
A maximum of...
Hello,
I have data in the following format:
<tbody>
Range Min
26
31
36
41
46
Range Max
30
35
40
45
50
Number of Occurences
211
134
652
121
89
</tbody>
How I can use Excel to get the value of a given percentile. So for example, the 15th percentile is 32, the 85th percentile is...
I have a large data set where the data is delineated by a field called "Category." For each Category, I'd like to calculate the PERCENTILE.INC for each row within that Category. For example, here is some sample data with the (manual) formula. I'd like a way to automatically calculate the range...
Hi,
I am trying to automate percentile report generation. Any help would be greatly appreciated.
Requirement:
1. I get 40 csv files with data everyday under a shared LAN folder. Folder name is current date and 40 csv files names remain same.
2. I need to calculate a percentile report everyday...
OK so here is my not working formula, which in fact needs to be extended to include references G3 and H3:
=PERCENTILE(IF(OR(E:E=E3,E:E=F3),L:L),Ref!$B$2)
The problem is the source data with formatting which means that there is not always a value in column E, sometimes it is in F or G or H, and...
So basically I have a column of numbers. I need to calculate the 25th, 50th, 75th, and 90th percentiles automatically. There is one blank row between the bottom of the column and my 25th percentile calculation. The VBA code below would work if the column was a fixed number of rows but there...
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.