Percentile Values With Different Combination
Mar 10, 2014I 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
ADVERTISEMENT
Lookup Values For Score Combination
Feb 21, 2014I have a data array as follows..
Data1.png
I have a requirement as follows..
I need system to fetch values for student/subject score combination, which are in random order.
Req1.png
Search Records With Combination Of More Than 2 Values
May 24, 2007i've written the code below but somehow the output is not what i want.
i have a multiple records and each records contain 30 rows of info i have 3000 over records and they are all in one sheet. i need to find record using more than 1 criteria. I've written the code below. how to use VBA to search for particular strings in cells?
for e.g.
A1 : hello
A2 : world
A3 : anyhow
i want to find the cell that contains "yh" using VBA, and the output should be in cells A3.....
Formula To Create Combination Of Text Values
May 22, 2014I need a formula that can provide all possible alphabet combination in another cell .
example
COLUMN A A1 = RED
COLUMN B B1:B50 = BELOW COMBINATION / RESULT
RED
ERD
DRE
EDR
DER
RDE
How To Generate All Combinations Of Ten Values List / Picking In 4 Each Combination
Oct 20, 2013I have a list in excel with ten values, but I need to get all combinations possible, picking 4 of 10 each combination. This must be as a combination and no permutation or variations either, I mean, values can't be repeated in a single combination.
I'm struggling to do this in a Macro but I don't get it.
Text Output Based On Combination Of Cell Values?
Mar 9, 2014I'm tying myself in knots when trying to combine logical formulas to return a text value based on multiple cell values.
Cell A1 can equal 1-10, and cell B1 can also contain the values 1-10.
I want C1 to display the text "YC" if B1 value is equal to 9 or 10. I want C1 to display the text "SC" if B1 value is 5-8, and A1 value is 4-10. Any other combination of values should return a blank cell.
I tried splitting this into two components before trying to make it fit into a single equation, and i can achieve the first condition of display "YC" =IF(AND(A1>=9,A1
Find Combination Of Cell Values Equaling A Specified Total
Sep 30, 2006I guess this would work in a similar manner as Solver, but where Solver tweaks cell values to equal a given total, I've got a set of cells and I need to find the combination that equals an amount in another cell. Is there a way to do this?
View 7 Replies View RelatedConditional Array / Percentile
Feb 8, 2014I'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).
Get Percentile Value Over Dynamic Range?
Jul 4, 2013I 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)
Quartile/Percentile/Rank
Jun 18, 2008I 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.
What Is The 90th Percentile Of The Defined Array Abc
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.
Returning All Data From A List Above The Nth Percentile
Feb 15, 2006Is 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...
Average Based On Percentile Of Different Column
Feb 26, 2013I 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 RelatedPercentile Applied To Portion Of Group Of Numbers
Nov 29, 2011I 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.
Find Numbers That Are In 25th And 75th Percentile
May 2, 2014I 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.
Excel 2007 :: How To Use Percentile Based On Bucket List
Jun 9, 2014I 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
Only Display Value If Combination In Two Columns Matches Combination In Two Other Columns
Jul 12, 2012I have a formula that I can't get to paste successfully in the forum - it keeps getting cut off?!? ... but I think I can probably simplify my explanation to get the answer I want anyway.
I need to only show the value from AUS!$H$2:$H$17 if the C2 & D2 combination are the same as the AUS!$B$2:AUS!$B$17 & $AUS!$C$2:AUS!$C$17 combination.
Conditional Median Formula Into Conditional Quartile / Percentile?
Apr 15, 2014Having 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)
IF And OR Function Combination
Mar 20, 2009I have 2 cells with numbers. In a 3rd cell I want to create a formula which looks at the 2 data cells and shows a value. The rules are the following: If C1 or C2 are bigger than Xthen C3=value1 else C3=value2. I have some basic excel knowledge but im not very familiar with functions. I'm using Excel 2007.
View 4 Replies View RelatedCombination Of Sum Macro?
Feb 28, 2014I need to consolidate several amount and make sure which of them falls into a sum of an amount.
I wonder if excel can find all the calculation in range A, and highlight those that equal to the cell D10 (example as per my attachment).
Calculation.xlsx
Combination Without Repetition
Jan 16, 2010how to create a list (I know how to calulate the count) of combinations without repetition when choosing 2,3,4 and 5 words from a set of 5 in Excel 2007.
e.g. Alpha,Bravo,Charlie,Delta,End
(AlphaBravo=BravoAlpha)
Choosing 2 = 10
Choosing 3 = 10
Choosing 4 = 5
Choosing 5 = 1
5 Column Combination?
Nov 13, 2013I have data in a 5x5 area and I would like a VB Script or Function that can give every possible combination of one number from each column added together.
i.e.
500 400 300 200 100 1500
400 300 200 100 500 1900
300 200 100 500 400 1800
200 100 500 400 300 etc...
100 500 400 300 200
Combination Of Numbers
Jun 22, 2007I have a list of numbers from 1-7 would look like this each number in a seperate cell.
3 5
1 2 3 5 6 7
2 4 5 6 7
6 7
4 6
6
I want to use one number from each row (which there is only 6 rows) and then find every number from 1-7 that will complete the sequence 1-7. So with the numbers above (using one number from each row) the only other numbers that could be used would be 3 or 5.
The combos that would work:
row 1 use 3 ----------- row 1 use 5
row 2 use 1 ----------- row 2 use 1
row 3 use 2 ----------- row 3 use 2
row 4 use 7 ----------- row 4 use 7
row 5 use 4 ----------- row 5 use 4
row 6 use 6 ---------- row 6 use 6
5 would complete ---------- 3 would complete
Remember the numbers and how many numbers in each row can change but will always be 1-7 and I always need to find every number that can complete the sequence 1-7 by using one number from each row.
Numeric Combination
Nov 15, 2008on combination of numbers
on the extreme left column, i have 23 numbers from A1:A23. All 23 numbers are in the form of 4 digit. For example A1 there is 1234, i need to display the possible 3 digit combination of this in the same row (like say 123,124,234,134 in B1,C1,D1 AND E1).
Another example in A2 there is 3545, i need to display 354,455,355 in the same row in B2,C2,D2
I need to perform this operation for the 23 numbers on the extreme left row. Can give me some hint on the code.
Show All Possible Combination Of Set Of Numbers?
Feb 10, 2006I am trying to show the all possible combinations of a set of numbers in Excel, in my case I think permutations are more appropriate to use. For example: there are three numbers 1, 2, 3 I want to show results like:
1, 2, 3
1, 3, 2
2, 1, 3
2, 3, 1
3, 2, 1
3, 1, 2
The functions in Excel available only give the total number, but I want to see these combinations!
Only Allow Cell To Contain Any Combination Of 2 Arrays?
Jan 23, 2014I was just wondering if it was possible to only allow cells in a worksheet to only allow values that are a combination of 2 arrays whether it be through data validation or other means. For example, if I have an array that has a b and c and a second one with 1 2 and 3, is it possible to only allow values a1 a2 a3 b1 b2 b3 c1 c2 c3?
View 2 Replies View RelatedMatch Offset Combination
Apr 6, 2009Can Offset be combined in an Index Match Match formula as per the attached sample?
View 4 Replies View RelatedLookup And Countif Combination
May 11, 2009I'm trying to use a combination of Hlookup and COUNTIF. I'm selecting a date value in a cell using data validation. I'm then wanting to write a formula to lookup that value in a row of dates, and then use a countif to find all the '1' values in that column.
View 6 Replies View RelatedTranspose And Vlookup Combination
Jul 25, 2009Ive attached my example but explaination of what i am trying to do is below:
In sheet 1 i have products listed with a product ref
In sheet 2 i have a list of features by product ref.
I want to be able to put each feature next to the relevant product in sheet 1, some products may have 3 features, others may have 5 or more.
Sumif & Sumproduct Combination
Oct 9, 2009I need combining sumif & sumproduct. I have attached a file which explains what I need.
View 2 Replies View Related