Get Percentile Value Over Dynamic Range?
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
ADVERTISEMENT
Dec 22, 2009
I have a range which will change in size & in content, & I want this to be a Named Range at whatever size it is.
Reason I want to is because I want to make a Validation List with this dynamic range. I also want a Validation list which lists the content of 2 or more dynamic ranges which may or may not be on the same worksheet - is this possible?
i.e.
First dynamic range: called "Milestones" at A11
Second dynamic range: called "Activities" at A25
& make a Validation list that will list content of both
View 9 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
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
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
Apr 3, 2008
Im sorting a dynamic range as mentioned in this Sorting a Named Range. My range is called drWarningTypes and is defined as:
=OFFSET(DataSource!$A$2,0,0, COUNTA(DataSource!$A:$A)-1,1)
When there is only one cell in the range, then running the following sort function includes A1 also in the search (and also adjoining columns).....
View 9 Replies
View Related
Jul 15, 2014
I have a column of data that keeps getting new information in it. what i need is a named range that i can use for a chart, first point in the named range will be the first value in the column, second point will be avg. of point 1 and 2, then 3rd point will be avg. of 1,2,3 etc.
View 1 Replies
View Related
Oct 28, 2009
I am trying to get to grips with the dynamic ranges for pivot tables. I have named a range data and in the refers to section put:
=OFFSET(Sheet2!A1,0,0,COUNTA(Sheet2!A:A),COUNTA(Sheet2!1:1))
I was hoping that then when i go to data > Pivot table and it asks for the range i could put =Data but it tells me that the range is invalid. I have attached a copy at the bottom.
View 3 Replies
View Related
Sep 13, 2007
My searches have not produced anything that I could apply to this situation.
I'm trying to write VBA that would:
1. Search a Workbook for Dynamic Ranges.
2. When a Dynamic Range is found the code would:
A. Determine the current coordinates for the range.
B. Change the "Refers To" value From "=OFFSET...." To "=Worksheet_Name $Column$Row:$Column$Row"
3. Save Changes.
4. Close File.
My apologies but I have very little experience in writing VBA. I understand about variables, arguments, and IF/THEN but just enough to use functions within Excel.
View 9 Replies
View Related
Nov 5, 2009
I have a range of data that will grow as the days of the month pass but I need to run calculations on this range. The data is 13 columns wide but the number of rows will increase daily. I remember using a formula in the Define Range that would automatically take into account new additions using the OFFSET function but cannot for the life of me think how exactly to do it.
View 4 Replies
View Related
Jul 7, 2014
Line of code that will Select a Named Range in this case I have Named a CELL "DataSummary" Need to use that named range by selecting 30 columns and 54 rows.
Range("DataSummary),(??,??) doesn't work.
View 1 Replies
View Related
Mar 6, 2008
I have been working on part of the code for my spreadsheet and it works fine in the spreadsheet “Databaseform” however when I copied the code to my master spreadsheet “Paul_PartLocDBCombo” it does not work, I get the error:
Method ‘ range’ of object ‘_worksheet’ failed
The code is then highlighted in yellow, the code is:
Set rng = wksPartsData.Range("a1", Range("a65536").End(xlUp))
Meaning this part is incorrect but I don’t know why? To work it: go to Databaseform and press start. Enter 7mm in the product field and press find all. It will then return all the matching results in the userform. Its this I want to try and achieve on the other spreadsheet when the button find label is pressed.
View 3 Replies
View Related
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
Jun 19, 2009
I have this macro named ABC to bring the name ranges to another worksheet. The name ranges are already saved in the workbook with the below name range. I used "***" so that this formular can go to the last cell that has "***" on A column and it worked untill someone typed "(xxx)" and the range now stops right before the "(xxx)"
=OFFSET(Storage_Team2!$A$1,0,0,MATCH("***",Storage_Team2!$A:$A,-1),37)
What would be the best to create the range according to the specific words in a column?
View 9 Replies
View Related
Jul 9, 2006
i am coloring cells:
Sheets("MySheet").Range("a1:f200").Interior.ColorIndex = 5
i want a dynamic range, so something that would look like this as an excel function:
=OFFSET('MySheet'!$A$1,0,0, COUNTA('MySheet'!$A:$A),6)
i want the equivalent of this programmed (hard coded) into vba
i don't want to reference to a defined name with vba, because the user can delete that.
View 9 Replies
View Related
Jul 15, 2007
I wonder if there is a way to Sum the total of a Dynamic Range so that the summary will be presented in the upper Cell of the summed column?
(please see the attached picture).
The reason is to anable FREEZING of the first row (display the totals all the time even when scrolling down) as the list will get very long after a while.
View 9 Replies
View Related
Jun 24, 2008
I am looking to sum a dynamic range and can't quite figure it out. The data expands down rows every week, and I want the sum formula (the final row) at the bottom to include the newly updated rows. I can't dynamically name the numbers to be summed because the dynamic named range will include the sum number and create a circular reference. For example:
5
4
3
2
14
the bottom row (14) is the sum. Next week, my numbers are
5
4
3
2
5
19
I have a macro that will automatically put the sum formula in the correct row and column, but can't quite figure out the formula.
View 7 Replies
View Related
Nov 1, 2012
I am currently recorded a macros for a sumif formula. But since the number of rows will not be same every time.
I want the code for a dynamic range.
VB:
Range("E7").Select
ActiveCell.FormulaR1C1 = _
"=SUMIF('V1 Pivot'!R5C2:R32C2,'Task Level Tab'!R7C2:R30C2,'V1 Pivot'!R5C3:R32C3)"
Range("E7").Select
[Code] ....
View 9 Replies
View Related
Dec 21, 2012
I need the code for formatting to be edited, so that it work dynamically for any range
VB:
Range("B16:L16").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
With Selection.Borders(xlEdgeTop)
[Code] .....
Here the columns "B" and "L" shall be constant, but the number 16 may vary depending upon the number of lines in the sheet.
View 1 Replies
View Related
Feb 15, 2013
I have column A fill with dates and column B with customer code. I'm looking to count the number of time the customer code (column B) is repeating but ONLY in previous year. Meaning that the count range must be adapt for each customer code. Will a countifs is able to work with this request?
View 6 Replies
View Related
Sep 15, 2014
I am working with loans, and I'm trying to program a routine that adds every installment a client owes, so I can know the total amount owed, depending on how many unpaid installments she has. Let say for a client I have every installment in column A (rows 1 to 5), and in column B, I want to add them all. The output should be
Cell B1: "sum(A1:A5)"
Cell B2: "sum(A2:A5)"
Cell B3: "sum(A3:A5)"... and so forth
Since I have many loans, I need a dinamic routine to that sums every installment, where amount of installments vary from client to client. My proposal is (adding from the last installment to the first one):
VB:
months = 4 'an example
For j = 0 To months - 1
initial_row = ActiveCell.Row 'because I run this several times. In this example I am in row 18[code]....
For some reason this is not working, and what I see in Excel after running it is: SUM('AJ18':'AJ18'), which doesn't work.
View 1 Replies
View Related
Jun 28, 2014
I have data in 2 columns on which I need to do vlookup. This range for vlookup increases. I want to name this dynamic range. How do I do it?
View 1 Replies
View Related
Nov 11, 2009
I'm trying to name a dynamic range for A2:B10000.... I know how to use the OFFSET function but was wondering to do it using the INDEX function
Both column will always have the same number of used rows.
Another question:
If I have dates in Column A, and values in Column B....what formula do I need to get me the last value (last row,most recent Date)
View 2 Replies
View Related
Feb 16, 2010
1. I have a worksheet with subtotal and total row above all columns
I currently use SUBTOTAL(9, A6:A6000) and SUM(A6:A6000)
however the no of rows is dynamic based on imported data so I want the range end (i.e. A6000) to also be dynamic i.e. refer to the last cell with data in the column.
Something like SUBTOTAL(9, A6:LastCell in A)
2. I also use SUMPRODUCT to do calculations
e.g. SUMPRODUCT (($B$6:$B$6000 = "ABC") * ($A$6:$A$6000))
again how can I make the sumproduct dynamically use all rows to the last active row.
3. I have tried just setting the second part of the range reference to a very large number but am worried that this slows down the calculation. Does it?
e.g. SUMPRODUCT(($B$6:$B$100000 = "ABC") * ($A$6:$A$100000))
View 9 Replies
View Related
