Determine the highest Matrix value (Office - Excel)

[RUI SERRA]

Dear collegues,

I have a table with columms and lines (4 x 13).
I need to determine the 13 highest values from the table.
I will appreciate your help

see you
Rui

 

[Chip Pearson]

Select the 13 cells that are to get the result. Type the following
formula and press CTRL SHIFT ENTER

=LARGE($A$1:$C$7,ROW(INDIRECT("1:13")))

Since this is an Array Formula, you *must* press CTRL SHIFT ENTER
rather than just ENTER when you first enter the formula
and whenever you edit it later. If you do this properly,
Excel will display the formula in the Formula Bar enclosed
in curly braces { }. (You do not type the curly braces -
Excel includes them automatically.) The formula will not work
properly
if you do not use CTRL SHIFT ENTER. See
http://www.cpearson.com/excel/ArrayFormulas.aspx for lots
more information about array formulas.

This formula will work when the 13 cells are in a single column, going
down 13 rows. If the 13 cells are all in one row, spanning 13 columns,
use the following formula and press CTRL SHIFT ENTER.

=TRANSPOSE(LARGE($A$1:$C$7,ROW(INDIRECT("1:13"))))

In both formulas, change $A$1:$C7 to your actual range of values.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)




On Sat, 31 Jan 2009 14:53:00 -0800, RUI SERRA <RUI

 

[xlmate]

Hi

Have you try using Sort in Excel?

Click on a header or a cell within your table
Go to Data on the menu bar
Choose Sort
In Sort by : select the column where your numbers are, eg column A
this will highlight your whole dataset.
checked Descending
checked on Header row if your dataset have header row
OK

The highest numbers are sorted to the top rows.
This assume that there are no duplicates in your range, otherwise, you need
to use the LARGE formula provided by Chip

HTH


--
Appreciate that you take a moment to click on the Yes button below if this
posting is helpful.

Thank You

cheers, francis