Quartile/Percentile/Rank
Jun 18, 2008
I have a collection of data where the lower the value, the better. When I use Quartile/Percentile, it shows higher values as having a higher rank. Any way to reverse the order, without changing the underlying data (ie. multiply all values by -1)?
Also, I have a graph of the data showing the 1st, 2nd (median) and 3rd quartile and then a particular data set (in this case, company results) over a number of years. Any way to automatically add the Percentrank as a data label for the specific company results? For example, the value 200 puts this company at the 65th percentile. I want the data label to show 65, not 200.
View 9 Replies
ADVERTISEMENT
Apr 15, 2014
Having trouble changing this formula from its current Median to a Quartile or Percentile. The median formula looks like this:
(MEDIAN(IF(DATA!$B$15:$DZ$15=1000,DATA!$B26:$DZ26))) and works perfect.
However I can't figure out how to manipulate it into a percentile keeping the condition of
IF(DATA!$B$15:$DZ$15=1000)
View 2 Replies
View Related
Dec 1, 2008
I need to find third quartile for a set of data. Eg:
Client Time Taken
ABC 00:25:27
XYZ 01:11:21
ABC 07:03:33
ABC 02:44:13
DEF 00:22:12
XYZ 00:33:23
ABC 00:22:18
ABC 00:27:12
DEF 00:18:23
............................. etc.
There are around 50 Clients, and the third quartile (of Time Taken) for each of these clients needs to be calculated.
Currently what I am doing is, I sort the entire data by Clint Name, and then write 50 formula (Each for each client).
View 13 Replies
View Related
Dec 10, 2008
Excel formula for 3rd Quartile = Quartile(Range, 3)
In my
Cells(LastRow + 1, 12).Formula =
My range is between Selection and Selectioin.End(xlUp)
So the resultant formula will be somewhat like
"=Quartile("Selection" : "Selection.End(XlUp)",3)"
View 12 Replies
View Related
Apr 23, 2007
The goal is to return the proper quartile based upon the results of the below parameters. Too many If, Then’s for me…
******** ******************** ************************************************************************>Microsoft Excel - Book3___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutC1=
CDEF1Current*Base*Salary***2Max*Salary***3Min*Salary***4****5****6Criteria*for*Quartile***7****81st*Quartile*-*If(current*base*salary<(max*salary*-*min*salary)/4+min*salary,*then*First*Quartile,*otherwise*"-"))***92nd*Quartile*-*If*greater*than*1st*Quartile*but*less*than*2nd,*then*2nd*quartile,*otherwise*1st*Quartile***103rd*Quartile*-*If*greater*than*2nd*Quartile*but*less*than*3rd*quartile,*then*3rd*quartile,*otherwise*2nd*Quartile***114th*Quartile*-*If*greater*than*3rd*Quartile*but*less*than*4th*quartile,*then*4th*quartile,*otherwise*3rd*Quartile***Sheet1*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
View 9 Replies
View Related
Dec 12, 2011
I have a bunch of raw sales data, by individual, which I would like to quartile to determine who is performing at which level.
Not all of this data should factor into the Quartile - a T/F "Exclude?" column flags the individuals who should not count in the quartile for any given month.
I then built a pivot like this with the Row Labels nest like such:
Exclude
Year
Month
Agent
What I would like to do is define a formula that will quartile all of the agents where Exlude is False, Year is 2011, and the Month is January. And I would like this work going forward, so manually defining the range each month would not be a fix.
What do you all think? Is there a way to do this? To tell it to only quartile the individuals for each month?
FALSE
..2011
.....January
.........Roseus.........5
.........Mr. Excel......6
........................3
.....February
View 3 Replies
View Related
Oct 8, 2013
Just curious to know of any other formulas similar to the "quartile" function that allow you to calculate results in thirds (plus any other if known)
View 2 Replies
View Related
Aug 1, 2008
Im looking to use vba to determin upper and lower quartiles, i know how to do this through excel "QUARTILE(B9:B59990,1)", but was wondering if anyone would of any code to do this in visual basic?
View 2 Replies
View Related
Feb 8, 2014
I'm looking to use the percentile formula given a condition. The percentile formula as I understand it is percentile (array,k), where K is the percentile I want.
What I want to do is select a column for the percentile, but only do it for values above zero (I don't want to get into the full explanation as to why but I cannot cheat and just sort the columns).
The context is that I was doing some averageif calculations previously and want to remove outliers (top 5 and bottom 5% of data).
View 1 Replies
View Related
Mar 10, 2014
I have set of people where by based on Level & designation want to find the percentile on their CTC.
Attached is the sample file. Percentile.xlsx‎
View 6 Replies
View Related
Jul 4, 2013
I have to run the 95th percentile value against a raw data dump from a monitoring tool. The data has multiple devices and each device has about a couple of thousands of samples/values. These values are dynamic, it can be 1000 samples for device 1 and may be 1300 for device 2. The data between 2 devices are separated by an empty row. run a macro using the percentile formula that can accept a dynamic range in the array so that it should not matter how many samples/values each device has, it should provide the 95 Percentile value.
Current Option: =Percentile(array,value)
What I'm looking for is: =Percentile(A dynamic range starting from a designated cell to all the way till the last cell in that range i.e before the empty row that separate the two devices,value)
View 3 Replies
View Related
Jan 31, 2014
I have a table with a set of duplicate values like given in the sample table below;
abc22
abc12
abc33
abc44
def60
def56
def46
ghi78
ghi88
ghi98
klm108
klm118
klm120
klm125
klm140
i need to derive the percentile value of one particular text in another sheet. i.e
i want to get- what is the 90th percentile of the defined array abc
if i use the formula percentile(vlookup(abc,table1,2),90%), the cell is referring only to the first value. i want the entire range to be referred.
View 1 Replies
View Related
Feb 15, 2006
Is there any function in Excel that returns all values from a list above or below a certain percentage rank/percentile?
For instance, if you want the average of the 2% highest numbers in a list? Or the sum of the 25% most expensive items in a shopping catalogue?
It has to be a "dynamic" function, where you can just drop in a set of figures, sort the list, add a percentile, and perform a calculation on all the figures from the cutoff point signalled by the percentile...
View 10 Replies
View Related
Feb 26, 2013
I have a list of 186 random numbers sorted by descending order in column A. In column B I have another statistic of corresponding values to the number in column A. I am hoping to calculate the average of values in column B based on the percentiles of column A. (i.e. for the top 10th percentile values of column A the average B value is X, for the top 20th percentile of values in column A, the average B value is Y).
View 2 Replies
View Related
Nov 29, 2011
I have a list of 400 numbers. I wish to filter down the list to those 500 and then of that list get the 95 percentile.
I have tried
=IF('GRML ECP 01'!O$2:O$584 500 I get a blank. When I run the function manually on the numbers > 500 I get 1800.
View 3 Replies
View Related
May 2, 2014
I use the following formula to pull the median from a list of numbers...
=MEDIAN(IF(($H$2:$H$7800=$H7813)*($T$2:$T$7800""),$T$2:$T$7800*0.01))
I'm trying to also find the numbers that are in the 25th and 75th percentile, is there an easy way to do this?
Column H = Performance Rating (1-5)
Column T = The bonus payout % (example 105%)
I'm trying to pull the 25th and 75th percentile for each rating. So if there are 100 employees rated 1, what is the bonus payout that is the 25th percentile? This is just an example, in this case it would be the 25th number in the list....I'm dealing with thousands of bonus %'s.
View 4 Replies
View Related
Jun 9, 2014
I am having trouble using formulas in a smart way in excel.Basically, I am trying to calculate percentile for a range. But since my data set is huge, I dont want to select the range in percentile formula manually. my data set includes buckets and would love to be able to search for that field and then calculate percentile on a range. is that possible?
example
see attached excel file -its a small data set, but there are more buckets in the volume column. Using Excel 2007
View 1 Replies
View Related
Dec 30, 2013
I'm trying to Rank a list and than re-rank the list while excluding certain (or by Criteria) items
Vendor Co
Cost Fee
Rank
Vertox
500
4
BV
1520
3
[code].....
View 4 Replies
View Related
Feb 11, 2009
What I am trying to do is give the rank in column D based on the values in columns B and C. Some of the values in column B will have then same rank, and as such I want to add further criteria on which to rank them. I would first like to rank the values in column B and then rank the values in column C, which should give the rank in column D. For example Dog and Frog have the same value of 400 from the Non UK column. Therefore, rather than having these as both rank 1, I want them to be ranks 1 and 2, so want to add another criteria (UK). As Dog is greater than Frog in the UK (i.e. 10>7), I would like to rank Dog as 1 and Frog as 2. Goat will be ranked as 3 because it had the thrid highest value in the Non UK.
ABCD
1Non UKUKRank
2Cat20055
3Dog400101
4Eel200114
5Frog40072
6Goat30023
View 4 Replies
View Related
Oct 28, 2009
I am stumped on this one. I a spreadsheet with 5 columns (Quarters, Months, Level or Rank, Name, Sales). I need to have a formula to pull in the new Level 1 (rank) after Q4 sales are summed up across months for each name. See the attachment for detail.
View 4 Replies
View Related
Feb 22, 2009
Is it possible to 'eliminate' the zero values when using =RANK? For instance, if you have data with values from +100 to -100 with some lines have '0' value, can they be ranked disregarding the zeero values.
View 3 Replies
View Related
Apr 2, 2009
I have and table that gets the average performance of couriers as a %. so.
courier 1...courier 2...courier 3...courier 4,
98.16.......100...........99.7...........99.72
i want is a formula (hoping note to have to code this) that will show which order these couriers should be ranked in by looking at the range of averages. So courier2(100) would be ranked 1st, courier 4(99.7) would be ranked 2nd etc. the end table will look something like.
courier 1...courier 2...courier 3...courier 4,
98.16.......100...........99.7...........99.72
4..............1...............3................2
View 2 Replies
View Related
Dec 8, 2012
I have a list of names that come from another sheet. I need to rank these by score and have used this formula:
[Code] ........
These names compete within their class but also within the class I need to consider (rank) a second category for example No or Yes. Also all names could be competing in the Yes category or for example 7 in the Yes and 3 in the NO. This will vary and is based on entry in another sheet.
The format is Class (column B), category (Yes or NO, Column C), Name (Column D), Score (Column S) and Rank (Column T)
If I include an IF statement =IF(S33="Yes", at the beginning of the above formula then I get all the Yes ranked perfectly. Is it possible to rank the others with the NO consideration?
View 4 Replies
View Related
Jan 31, 2012
I have a spreadsheet of raw data organised in columns and rows. one of my columns is called SERVERNAME, and each row contains the data about an incident which is related to that server.
What I need to be able to do is to firstly calculate via a formula (cannot be a pivot table) the number of instances of SERVER X, and then I need to be able to rank the top 10 servers for which records have been logged.
As an example here is some raw data:
INCIDENT
SERVERNAME
LOCATION
OS
OWNER
0001
Server 1
Japan
Windows 2008
KERLEJ
[Code] ........
Based on the above data I would like to have an Excel formula that searches through the records (Rows) and determined that there are the following number of instances of servers:
server 1 2
server 2 1
server 3 3
And then a further formula (again I cannot use pivot table) to calculate the ranking:
Server 3 3 - ranked first
server 1 2 - ranked second
server 2 1 -space ranked third
View 9 Replies
View Related
Mar 9, 2012
here is some code that I have,
Dim zima As Range
Dim Col As Long
On Error Resume Next
Set zima = Application.InputBox("Select column to paste to", Type:=8)
On Error GoTo 0
If zima Is Nothing Then Exit Sub
[code]....
Where in the formula C[-7], I need this to line up with the column that I selected via the InputBox. If I select column L then the -7 needs to change to +2. OR would there be a better way to write the formula in VBA?
View 7 Replies
View Related
May 4, 2013
trying to use the rank function but unable to use different ranges, even if I use named ranges
=rank(F1,F1:F30,F50:F60,F90:F115,0)
=rank(F1,F1:F30,F50:F60,F90:F115,0)'
It says entered too many arguments
View 5 Replies
View Related
Nov 21, 2013
I have created a sheet (call it sheet2) with 5 columns as per below snapshot. There's quite a lot of data as much as 10k rows.
Rank
Total
city
[Code]....
What am trying to do is create a summary table which shows the top 5 cities per region based on the population. This summary table will be on a separate sheet in something like the table below. formula that can lookup a region say Europe and show the top 5 cities based on the rank already calculated on sheet2 and also show the total in a separate column.
Rank
Region
City
total
1
Europe
[Code] ........
View 9 Replies
View Related
Jul 23, 2006
I have 10 sales figures in C2:C11.
In D2:D11, I want the rank of those values, with one twist.
If the value in B2:B11 is X, then that value is excluded from the ranking.
I am trying to basically do:
=RANK(C2,IF(B2:B11="X",0,C2:C11))
View 9 Replies
View Related
Apr 9, 2007
I deal with Income statements of varying length. What I'm trying to do is figure out a way to rank only the expenses.
The account names change from sheet to sheet, and the number of accounts always changes.
However, Expenses always START after a cell in the "A" column marked "OPERATING EXPENSES" and always ENDS after a cell in the "A" column marked "TOTAL OPERATING EXPENSES". However, I don't want to rank "TOTAL OPERATING EXPENSES" because it's the sum of all the indivicual expenses.
View 9 Replies
View Related
Oct 28, 2007
A1 has column heading Name.
B1 has column heading Credits.
C1 has column heading Rank
A2 has student's name and B2 has his Credit value
Row 3,4,5 are blank.
A6 has a student's name and B6 has his credit value.
Row 7,8,9,10,11 are blank.
A12 has a student's name and b12 has his credit value. So on so forth.
All I need to do is rank those students in C2, C6, B12 so on so forth.
Its taking a lot of time and I am not getting any result. I tried reading about rank but it tells about asc/desc order, which I dont understand how it fits in above example. I just need rank of a student at a particular cell.
View 9 Replies
View Related
