| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Top 5 of the series
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 ? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Top 5 of the series
Hi,
Put this in a cell and drag down 4 rows to get the top 5 in the range =LARGE($A$1:$A$50,ROW(A1)) Mike "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 ? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Top 5 of the series
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 ? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Top 5 of the series
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 ? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Top 5 of the series
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 ? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Top 5 of the series
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 ? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Fill Series Dates: not letting me change the series from year to m | Excel Discussion (Misc queries) | |||
| how to plot column chart with one series against multiple series. | Charts and Charting in Excel | |||
| chart data series -- plot a table as a single series | Charts and Charting in Excel | |||
| how do I change a line series to a column series in excel? | Charts and Charting in Excel | |||
| series graph -- one series being added to another series | Charts and Charting in Excel | |||
Linear Mode
