Ranking data with fuctions

[douglascaixeta]

Hi,

I have to following table:

<!--This file was converted to xhtml by OpenOffice.org - see http://xml.openoffice.org/odf2xhtml for more info.--><link rel="schema.DC" href="http://purl.org/dc/elements/1.1/" hreflang="en"><link rel="schema.DCTERMS" href="http://purl.org/dc/terms/" hreflang="en"><link rel="schema.DCTYPE" href="http://purl.org/dc/dcmitype/" hreflang="en"><link rel="schema.DCAM" href="http://purl.org/dc/dcam/" hreflang="en"><style type="text/css">
@page { }
table { border-collapse:collapse; border-spacing:0; empty-cells:show }
td, th { vertical-align:top; font-size:10pt;}
h1, h2, h3, h4, h5, h6 { clear:both }
ol, ul { margin:0; padding:0;}
li { list-style: none; margin:0; padding:0;}
li span.odfLiEnd { clear: both; line-height:0; width:0; height:0; margin:0; padding:0; }
span.footnodeNumber { padding-right:1em; }
* { margin:0; }
.Default { font-family:Arial; }
.ce1 { font-family:Arial; border-width:0.0133cm; border-style:solid; border-color:#000000; text-align:center ! important; margin-left:0cm; }
.co1 { width:0.554cm; }
.co2 { width:0.499cm; }
.co3 { width:2.267cm; }
.ro1 { height:0.427cm; }
<!-- ODF styles with no properties representable as CSS -->
.ta1 { }
</style><table class="ta1" border="0" cellpadding="0" cellspacing="0"><colgroup><col width="24"><col width="22"><col width="22"><col width="22"></colgroup><tbody><tr class="ro1"><td style="text-align: left; width: 0.554cm;" class="Default">
</td><td style="text-align: left; width: 0.499cm;" class="Default">
</td><td style="text-align: left; width: 0.499cm;" class="Default">
</td><td style="text-align: left; width: 0.499cm;" class="Default">
</td></tr><tr class="ro1"><td style="text-align: left; width: 0.554cm;" class="ce1">X
</td><td style="text-align: right; width: 0.499cm;" class="ce1">1
</td><td style="text-align: right; width: 0.499cm;" class="ce1">4
</td><td style="text-align: left; width: 0.499cm;" class="ce1">
</td></tr><tr class="ro1"><td style="text-align: left; width: 0.554cm;" class="ce1">Y
</td><td style="text-align: right; width: 0.499cm;" class="ce1">7
</td><td style="text-align: left; width: 0.499cm;" class="ce1">
</td><td style="text-align: right; width: 0.499cm;" class="ce1">5
</td></tr><tr class="ro1"><td style="text-align: left; width: 0.554cm;" class="ce1">Z
</td><td style="text-align: right; width: 0.499cm;" class="ce1">0
</td><td style="text-align: right; width: 0.499cm;" class="ce1">3
</td><td style="text-align: right; width: 0.499cm;" class="ce1">9
</td></tr></tbody></table><table class="ta1" border="0" cellpadding="0" cellspacing="0"><colgroup><col width="99"></colgroup><tbody><tr class="ro1"><td style="text-align: left; width: 2.267cm;" class="Default">
</td></tr></tbody></table><table class="ta1" border="0" cellpadding="0" cellspacing="0"><colgroup><col width="99"></colgroup><tbody><tr class="ro1"><td style="text-align: left; width: 2.267cm;" class="Default">
</td></tr></tbody></table>

And I would like to show the biggest number in each column. Like a ranking, of 2. I don't want to use filter because I want just the biggest 5 and maybe the smallest 5. And the original database has more than 100 number on the column.
So if there is a formulation that give me for example:

The biggest is: Y 7, X 4, Z 9
The smallest is: Z 0, Z 3, Y 5

How can I do this?

Thanks in advance.

 

[Special-K99]

Try this

=INDEX(A1:D65535,MATCH(MAX(B1:65535),B1:B65535,0),1)&" "&MAX(B1:B65535)

and use MIN instead of MAX to find the lowest values.


What happens if there are duplicate values in the columns?

 

[douglascaixeta]

Hi,

Thanks it works! But how can a make the second highest and the second lowest?

When there is 2 equal values, it gets the first that he founds.

 

[douglascaixeta]

Hi,

I get it.
To make the ranking with first, second,... position, I use the same formulation, but instead of MAX and MIN I need LARGE and SMALL. Then I can select the position.