Conditional Array / Percentile
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
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
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
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
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
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
Apr 16, 2014
I have a challenge, which i have attached a spreadsheet. It's probably easier if you look at the spreadsheet, rather than me trying to explain. I've added some notes for reference.
I've tried to use an IF/vlookup function but I cant seem to make it work.
Attached File : ans.xlsx
View 9 Replies
View Related
Nov 28, 2008
Attached is the file where I want to derive Min Date of seq A and asset Type Char.
SeqAsset TypeStDateEndDate
AChar21-11-200828-11-2008
BEnv22-11-200829-11-2008
CProp30-10-200830-11-2008
AChar03-11-200801-12-2008
AChar04-11-200801-12-2008
AChar05-11-200801-12-2008
AChar06-11-200801-12-2008
AChar07-11-200801-12-2008
I want to find the Minimum Date of Seq "A" and of Asset type "Char". I used following Array Formulas but showing the correct answer 30-10-2008MIN(IF(A2:A9="A",C2:C9="Char"),(D2:D9)) But Istead of 03-11-2008 it's showing 30-10-2008 date of seq C and of asset type Prop
View 2 Replies
View Related
Aug 21, 2013
I am trying to use conditional formating that will highlight any cell in the range M2:P9 are greater than 0 AND where M1:P1 (text) does NOT equal Q2:Q9 (text).
View 3 Replies
View Related
Nov 1, 2013
I need to sum several non-contiguous columns if certain other columns meet a certain criterion.
Details:
Need to sum columns Q,R,S,T,U,V,W,X,Y,Z,AC,AD,AI,AJ,AK,AL,AM,AN,AP,AQ,AR,AS,AT,AU,AV,AX,AY,AZ,BA,BC,BD,BE,BF,BH,BI,BK,BL,BM,BN,BQ,BR
in sheet "(1) 65539004 SAP"
IF
column D in sheet "(1) 65539004 SAP" = A2 in sheet "(4) Brand Breakout" AND column L in "(1) 65539004 SAP" = $B$1 in "(4) Brand Breakout"
The formula will be placed in C2 in "(4) Brand Breakout" and copied to other cells just FYI,
View 9 Replies
View Related
Jun 19, 2014
How to conditionally format an array formula? The basic formula is an index(match criteria1 and match criteria2. I have come across sites where they mention you can incorporate aggregate into the formula to remove the Alt+Ctrl+Shift thus allowing for the formula to work in conditional formatting. Shown below is the formula I am trying to use. Basically if the formula below = today() then be yellow.
{=IFERROR(INDEX(Table2[Comp Req Date],MATCH(1,([@[SLDR_EF]]=
Table2[Component Material])*([@[Grand Sequence]]=Table2[Order Seq Num]),0)),"")}
View 2 Replies
View Related
Feb 21, 2007
I have this :
ColumnRow
1 7
1 27
5 3
5 7
5 31
5 35
5 39
5 59
9 7
9 11
9 31
I need a formula to use with conditional formatting that "lights up" the corresponding cells. So, in this case cells A7, A27, E3... should "light up".
Is this possible, or should I use VB?
View 9 Replies
View Related
Feb 26, 2007
I've got a big database dump from SAP. For simplicity, let's say it looks like:
Item# Supplier Model Description ItemCategory
1 A A-1 Something Tool
2 A A-24 Somethinel Tool
3 B R45 Bla Blah Kit
4 C N75 SoandSo Tool
etc. with thousands of entries.
I want to be able to manipulate members of this data set that have "Tool" in the item category alone.
Is there a way to build an array based on a conditional factor of this kind?
View 9 Replies
View Related
Feb 21, 2009
I have a 125x125 array of numbers each cell of which is equal to the row header divided by the column header. Using conditional formatting in Excel2007 I can visually identify the integer values. However, for each integer value, I want to list in 3 columns the cell value, the row header value (or reference) and the column header value (or reference).
View 9 Replies
View Related
Jun 23, 2014
I have the following data; B1 and B2 are entered by the user and are named 'heat' and 'race' respectively. Conditional Array Formula Example.xlsx I'm after the ROW number that matches the minimum time (col C) for the heat and race combination shown - in the example below (heat: A, race: 100m), I'd like row '5' returned (or, better still, row '2' of the array A4:D11), as that's the lowest time for Heat A, 100m.
A
B
C
D
[Code].....
but this dosesn't exactly seem elegant, and doesn't work!
ie I simply want the row number (ideally relative to the range) for the lowest time for any given inputs 'Race' and 'Heat'.
View 3 Replies
View Related
Feb 25, 2009
This has been kicking my can all morning! Should be simple. I'm trying to conditionally format an array from the value of a single cell.
=IF(A1<80, A3:A24,RED,0)
=IF(A1>80,A3:A24,GREEN,0)
=IF(A1>120,A3:A24,BLUE,0)
View 2 Replies
View Related
Jul 16, 2009
Basically, I'm trying to alter the code by inserting another condition where if S12 = R than it will exclude the value found in D12, other wise D12 is included. It was initally setup with a range of $C12:$H12 but i had to write it out long to exclude $D12
Here's my ammended ...
View 9 Replies
View Related
Mar 28, 2014
I'm trying to find a single conditional formula which summarizes multiple text occurrences in a range of cells. I managed to summarize the occurrences in a single cell, but my challenge is that I'm looking for a single (array?) formula for this.
I have a worksheet with column A which contains the condition and column B which contains a text field. I'm looking for a single formula to summarize all the 'AB' occurrences in the cells B4..B11 where the column A is Apple. I managed to do this by using an additional column (column C), however as my worksheet contains over 10000 rows, I would like to use a single conditional (array?) formula summarize the AB occurrences (multiple ' AB's can occur in a single cell) in stead of having to use an additional field per row.
I have attached a sheet as an example. I'm looking for a single array formula in cell C11 which summarizes the 'AB' occurrences in cells B4..B11 where column A is Apple.
View 3 Replies
View Related
Jul 30, 2012
in C a string is nothing more than an Array of characters ending with a null character.
in VBA this does not seem to be the case.I am trying to use the BlowFish code from David Midkiff for some encryption, however the code sometimes fails:
When encrypting a string a string of a specific length should be returned. however sometimes one of the characters of the encrypted string is a null character, and the returned encrypted string (with a embedded null character) is causing me problems. I want to check for the null character and if present redo the encryption. But how do I check for the presence of this null character in a unicode (double-byte) string?
Checking with Len(encrypted) gives the correct length (!) but trying to move each (unicode)character into an array fails when using the Mid() function past the null character in the string.
Now I can use
byteArray() = StrConv(unicodetext,vbFromUnicode)
to get the lower byte of each character into an array easily, but how do I do this for both bytes of the unicode characters?
Using an integer array does not work other than through
intArray(j) = CInt(AscW(Mid(Outp, j, 1)))
which fails on the nullstring in my encrypted text.
I have tried with variants but so far no luck. I thought if the string was entered into a variant then I could also address the variant as an array of bytes, but that does not seem to be accepted by VBA.
View 1 Replies
View Related
Sep 9, 2012
I am trying to populate many arrays with the same code using something like this. For this test, assume the following data in A1.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
Code:
Sub populate()
Dim firstArr(5), secondArr(5), thirdArr(5), fourthArr(5), fifthArr(5) As Integer
Dim r, c, num As Integer
[Code]....
The above code does not work of course and falls over. I am unsure whether I should try and concatenate with something like this eg "" & arrName(i) = Cells (r,c) or go down a different route.
View 6 Replies
View Related
Dec 20, 2013
I need to export this to Xcelsius which doesn't support any macros/vba. Btw I can;'t use Row() in xcelsius too.
[Code].....
View 4 Replies
View Related
Oct 2, 2008
I have a class module with several private variables, including one that is an array of a user-defined type. I am trying to set the values of a single element of this array with "Property Let ..." from a string array:
View 4 Replies
View Related
Apr 7, 2009
Say I have 3 columns of data: A1:C10 and I want to run a Match() function on them all together to see if I get a match any one those cells, say the value of have in X1.
Since, Match only allows a One-Column lookup array.. is there a way to "concatenate" or "append" the 3 columns together within a formula so now I would be looking to Match in an array that is 1 column * 30 rows?
Basically want to convert =Match(X1,A1:C10,0) to =Match(X1,A1:A30,0) without moving around the raw data in the sheet.
And I want to avoid doing an AND or OR formula that uses 3 separate MATCH() for each column.
I have a hunch that the MMULT or MMULT/TRANSPOSE functions are involved, but can't seem to get it right.
View 6 Replies
View Related
May 2, 2013
I'm only starting to get to grips with arrays. I have what I consider to be a lot of data that I need to 'cut' into separate workbooks. I have written some code that does this by simply looping through each line, 250k+, checking against a variable and copying the row into a separate sheet. This took longer than it would have doing it manually. It was suggested to me that I use arrays to speed up the process. I have managed to store the test data into an array but am struggling to find a way to loop through and pull out an entire 'row' from the array based on a variable. I have looked for 2 days in various places to find some way to loop through the data held in the array, but to no avail.
That code will appear here from about 8am GMT tomorrow. I know that once I've cracked this I'm on the road to some very significant time saving and comprehensive report writing.
View 9 Replies
View Related
Aug 5, 2014
I want to pass an array to Offset in the "Height" parameter, without having to type the array.
{=MAX(SUBTOTAL(9,(OFFSET(A1,ROW(1:5),,{1,2,3,4,5}))))}
I can't seem to figure out how to build the {1,2,3,4,5}. I've tried another ROW(1:5) and have tried nesting that like N(ROW(1:5)) but nothing works.
How I can get the {1,2,3,4,5} without having to type it out (so that I can expand this to a larger list)??
View 8 Replies
View Related
