Thanks for Chip, it works.

Could you explain how the formula works ?

"Chip Pearson" wrote:

Select your cells, say A1:A10. Open the Conditional Formatting dialog
from the Format menu. Change "Cell Value Is" to "Formula Is", and
enter the following formula:

=NOT(ISERROR(MATCH(A1,LARGE($A$1:$A$10,ROW(INDIREC T("1:5"))),0)))

Select your format and click OK. The largest 5 elements will be
formatted.

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


On Wed, 28 Jan 2009 08:21:01 -0800, yclhk
wrote:

Hi, Thanks for Mike & Chip. Both ways are worked.

However, I wish to show the top five result within the original series of
numbers. I had tried to enter the formula in the formula field of
conditional formatting but not work.

Thanks again,

yclhk

"Chip Pearson" wrote:


Assuming the series of numbers is in A1:A10, select the cells in which
you want to top five elements to be returned, type the formula

=LARGE($A$1:$A$10,ROW(INDIRECT("1:5")))

and press CTRL SHIFT ENTER rather than just ENTER. 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.

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


On Wed, 28 Jan 2009 04:45:01 -0800, yclhk
wrote:

How to show the top 5 of a series of numbers ? The numbers are updated
regularly. Formatting cells setting is expected but how to set the function
formula ?