Top 5 from sub-array

[morsagmon]

Hi.

I have a table (PipelineTable) with these columns:
P
Amount

I need to extract the top 5 amounts from the rows that meet a criteria: P is a fraction (between 0 and 1).
For example, if this is the PipelineTable:

P	Amount
0.5	25
0	50
0.75	45
1	12
0.5	20
0.75	25
0.25	8
1	30
0.75	12

<tbody>
</tbody>

The top 5 results I'm after would be:
45
25
25
20
12

I'd prefer not to code a script for this and use a direct formula.
Using Array Formulas is fine if needed.

Thanks!
Mor

 

[krykm]

Row\Col

A​

B​

C​

<tbody>
</tbody>

1​	P	Amount	Top 5
2​	0.5​	25​	45​
3​	0​	50​	25​
4​	0.75​	45​	25​
5​	1​	12​	20​
6​	0.5​	20​	12​
7​	0.75​	25​
8​	0.25​	8​
9​	1​	30​
10​	0.75​	12​

<tbody>
</tbody>

Row\Col	C​
2​	=LARGE(IF($A$2:$A$10<>INT($A$2:$A$10),IF(LARGE($A$2:$A$10,ROW(A1)),$B$2:$B$10)),ROW(A1))​

<tbody>
</tbody>

Ctrl+Shift+Enter

 

[morsagmon]

<tbody>
</tbody>

Row\Col	C​
2​	=LARGE(IF($A$2:$A$10<>INT($A$2:$A$10),IF(LARGE($A$2:$A$10,ROW(A1)),$B$2:$B$10)),ROW(A1))​

<tbody>
</tbody>

Ctrl+Shift+Enter

Thanks! works great for the Amount column. However, trying to replicate for other columns in the table, either don't fetch the correct values (for numerical columns) or doesn't work at all (for text columns).
How can we make that more generic for any column I want to fetch (still based on the same P criteria and rank of amount)?

Mor

 

[krykm]

=LARGE(IF($A$2:$A$10<1,IF($A$2:$A$10>0,$B$2:$B$10)),ROW(A1)) Ctrl+Shift+Enter
=LARGE(IF("here your criteria","here the range you want to see tops"),"ROW(A1)")
ROW(A1)=1 when you drag it down then will be ROW(A2)=2 and so on. It means first large number, then for second row second large number and so on.

I didn't quite understand about the text. If still doesn't work, would you mind putting an example with texts?

 

[morsagmon]

Thanks, krykm.

I figured out the logic of your formula. Here's what I'm aiming for:

P	Amount	Description	ID
0.5	12	Third Record	00548
1	25	Not selected	10155
0.25	20	First record	05684
0	18	Not Selected	00048
0.75	15	Second record	60588
0.5	12	Fourth Record	55001
0.5	10	Fifth Record	01015

<tbody>
</tbody>

The resulting table will look like this:

ID	Description	Amount
05684	First record	20
60588	Second record	15
00548	Third Record	12
55001	Fourth Record	12
01015	Fifth Record	10

<tbody>
</tbody>

(I have another column tot include, probably not necessary here for the explanation).

I don't have a unique ID column and I don't want to add a helper column to that table. Need to identify records handle by row number probably...

Thanks!
Mor

 

[krykm]

Row\Col	A​	B​	C​	D​	E​	F​	G​	H​
1​	P	Amount	Description	ID		ID	Description	Amount
2​	0.5	12​	Third Record	548​		5684	First record	20​
3​	1	25​	Not selected	10155​		60588	Second record	15​
4​	0.3	20​	First record	5684​		548	Third Record	12​
5​	0	18​	Not Selected	48​		55001	Fourth Record	12​
6​	0.8	15​	Second record	60588​		1015	Fifth Record	10​
7​	0.5	12​	Fourth Record	55001​
8​	0.5	10​	Fifth Record	1015​

<tbody>
</tbody>

Row\Col	H​
2​	=LARGE(IF($A$2:$A$8>0,IF($A$2:$A$8<1,$B$2:$B$8)),ROW(A1))​

<tbody>
</tbody>

Row\Col	G​
2​	=INDEX($C$2:$C$8,SMALL(IF($B$2:$B$8=H2,ROW($C$2:$C$8)-ROW($C$2)+1),COUNTIF(H2:$H$2,H2)))

<tbody>
</tbody>

Row\Col	F​
2​	=INDEX($D$2:$D$8,MATCH(H2&G2,$B$2:$B$8&$C$2:$C$8,0))

<tbody>
</tbody>

First put the formula to H2, drag down. Then G2, drag down. Then F2, drag down.

For each of them Ctrl+Shift+Enter

Hope helps this time

 

[azumi]

Assumed your table in ranges A1:D8 (Including header:

For ID :

=IF(ROWS($A$1:A1)<=5,INDEX($D$2:$D$8,MATCH(LARGE(IF(($B$2:$B$8>0)*($C$2:$C$8<>"Not selected"),$B$2:$B$8+ROW($B$2:$B$8)/1000000,0),ROWS($A$1:A1)),IF($B$2:$B$8>0,$B$2:$B$8+ROW($B$2:$B$8)/1000000,0),0),0),"")

For Description :

=IF(ROWS($A$1:A1)<=5,INDEX($C$2:$C$8,MATCH(LARGE(IF(($B$2:$B$8>0)*($C$2:$C$8<>"Not selected"),$B$2:$B$8+ROW($B$2:$B$8)/1000000,0),ROWS($A$1:A1)),IF($B$2:$B$8>0,$B$2:$B$8+ROW($B$2:$B$8)/1000000,0),0),0),"")

For Amount :

=IF(ROWS($A$1:A1)<=5,INDEX($B$2:$B$8,MATCH(LARGE(IF(($B$2:$B$8>0)*($C$2:$C$8<>"Not selected"),$B$2:$B$8+ROW($B$2:$B$8)/1000000,0),ROWS($A$1:A1)),IF($B$2:$B$8>0,$B$2:$B$8+ROW($B$2:$B$8)/1000000,0),0),0),"")

Array Entered.........

 

[morsagmon]

Yesssss,
Thanks, krykm, works!
I had to fix a little something in the second formula (no need to add 1 to the headers row).

 

[morsagmon]

Thank you, azumi, for your offer.

You missed my initial post up the thread, so your formulas are not aiming at what I need. The description is not the criteria. krykm's solution works in my environment now.
Thanks!
Mor

 

[Aladin Akyurek]

@morsagmon

Your specs (original post) are not very clear (regarding p values, for example).

Here is a system for Top 5 (N) calculations you can adjust/adapt to your situation:
http://www.mrexcel.com/forum/excel-questions/342541-top-5-numbers.html#post1690421