LARGE function with only positive value/SMALL function with only negative value

[Chers]

Hello, i've been searching how can i just have positive numbers in LARGE function and only negative numbers in SMALL function.

[TABLE="width: 367"]
<colgroup><col span="2"><col span="2"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD] [/TD]
[TD="colspan: 2"]Weight[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]City[/TD]
[TD]2013-14[/TD]
[TD]2014-15[/TD]
[TD]Variance[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]HKG[/TD]
[TD="align: right"]12,930,136[/TD]
[TD="align: right"]12,837,363[/TD]
[TD="align: right"]-92,773 [/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]SHA[/TD]
[TD="align: right"]15,207,019[/TD]
[TD="align: right"]14,698,326[/TD]
[TD="align: right"]-508,692 [/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]TPE[/TD]
[TD="align: right"]3,850,784[/TD]
[TD="align: right"]6,529,257[/TD]
[TD="align: right"]2,678,473[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]BJS[/TD]
[TD="align: right"]7,103,738[/TD]
[TD="align: right"]4,362,969[/TD]
[TD="align: right"]-2,740,769 [/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]SEL[/TD]
[TD="align: right"]4,891,145[/TD]
[TD="align: right"]5,547,306[/TD]
[TD="align: right"]656,161[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]TUN[/TD]
[TD="align: right"]1,143,821[/TD]
[TD="align: right"]1,375,594[/TD]
[TD="align: right"]231,774[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]DUR[/TD]
[TD="align: right"]2,229,416[/TD]
[TD="align: right"]2,228,554[/TD]
[TD="align: right"]-862 [/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD]MRU[/TD]
[TD="align: right"]1,917,198[/TD]
[TD="align: right"]1,866,641[/TD]
[TD="align: right"]-50,557 [/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD]CAI[/TD]
[TD="align: right"]2,677,918[/TD]
[TD="align: right"]1,577,365[/TD]
[TD="align: right"]-1,100,553 [/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD]ALG[/TD]
[TD="align: right"]650,493[/TD]
[TD="align: right"]1,105,859[/TD]
[TD="align: right"]455,367[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD]DOH[/TD]
[TD="align: right"]3,314,611[/TD]
[TD="align: right"]2,361,114[/TD]
[TD="align: right"]-953,496 [/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD]KUL[/TD]
[TD="align: right"]4,916,556[/TD]
[TD="align: right"]5,478,833[/TD]
[TD="align: right"]562,277[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD]MCT[/TD]
[TD="align: right"]1,872,578[/TD]
[TD="align: right"]1,343,475[/TD]
[TD="align: right"]-529,103 [/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD]PER[/TD]
[TD="align: right"]3,931,913[/TD]
[TD="align: right"]3,412,003[/TD]
[TD="align: right"]-519,911 [/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD]CPT[/TD]
[TD="align: right"]3,499,113[/TD]
[TD="align: right"]3,965,778[/TD]
[TD="align: right"]466,665[/TD]
[/TR]
</tbody>[/TABLE]

I get this result when i filter some of the fields:

[TABLE="width: 305"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Rank[/TD]
[TD]Bottom 10[/TD]
[TD][/TD]
[TD]Top 10[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]-2,740,769 [/TD]
[TD][/TD]
[TD="align: right"]2,678,473[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]-1,100,553 [/TD]
[TD][/TD]
[TD="align: right"]656,161[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]-953,496 [/TD]
[TD][/TD]
[TD="align: right"]562,277[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]-529,103 [/TD]
[TD][/TD]
[TD="align: right"]455,367[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]-50,557 [/TD]
[TD][/TD]
[TD="align: right"]231,774[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]-862 [/TD]
[TD][/TD]
[TD="align: right"]-50,557 [/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]455,367[/TD]
[TD][/TD]
[TD="align: right"]-529,103 [/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]562,277[/TD]
[TD][/TD]
[TD="align: right"]-1,100,553 [/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: center"]#NUM![/TD]
[TD][/TD]
[TD="align: center"]#NUM![/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: center"]#NUM![/TD]
[TD][/TD]
[TD="align: center"]#NUM![/TD]
[/TR]
</tbody>[/TABLE]

So even if it's bottom 10, if there's only 6 negative values on bottom column, it should not include the positive values. In the same way, for top 10, if there are only 5 positive numbers, then it should not take the negative values.

I could not understand the array but i believe that's the only way hence would appreciate anyone's help.

Rgds,
Chers

 

[steve the fish]

Hi. I think this is what you want. Enter CNTL-SHIFT-ENTER.

=IFERROR(LARGE(IF($D$3:$D$17>=0,$D$3:$D$17),ROWS($A$1:A1)),"")
=IFERROR(SMALL(IF($D$3:$D$17<=0,$D$3:$D$17),ROWS($A$1:A1)),"")

 

[Chers]

Oh steve the fish - exactly what i needed! Thank you.