Percentile Values With Different Combination

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‎

Lookup Values For Score Combination

Feb 21, 2014

I 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, 2007

i'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, 2014

I 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, 2013

I 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, 2014

I'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, 2006

I 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?

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).

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)

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.

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, 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...

Average Based On Percentile Of Different Column

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).

Percentile Applied To Portion Of Group Of Numbers

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.

Find Numbers That Are In 25th And 75th Percentile

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.

Excel 2007 :: How To Use Percentile Based On Bucket List

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

Only Display Value If Combination In Two Columns Matches Combination In Two Other Columns

Jul 12, 2012

I 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, 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)

IF And OR Function Combination

Mar 20, 2009

I 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.

Combination Of Sum Macro?

Feb 28, 2014

I 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, 2010

how 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, 2013

I 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, 2007

I 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, 2008

on 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, 2006

I 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, 2014

I 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?

Match Offset Combination

Apr 6, 2009

Can Offset be combined in an Index Match Match formula as per the attached sample?

Lookup And Countif Combination

May 11, 2009

I'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.

Transpose And Vlookup Combination

Jul 25, 2009

Ive 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, 2009

I need combining sumif & sumproduct. I have attached a file which explains what I need.