| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Question
Here is my problem.
Say I have a list of 20 baseball player names in one column and how many home runs that they have in the next column. How can I make a cell = all the players names that have over 20 home runs? Please help. Thank you. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel Question
One way:
A1:A20 = player B1:B20 = runs Enter this array formula** in D1 and copy down to D20: =IF(ROWS(D$1:D1)<=COUNTIF(runs,20),INDEX(player,SM ALL(IF(runs=20,ROW(player)-MIN(ROW(player))+1),ROWS(D$1:D1))),"") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "magnello27" wrote in message ... Here is my problem. Say I have a list of 20 baseball player names in one column and how many home runs that they have in the next column. How can I make a cell = all the players names that have over 20 home runs? Please help. Thank you. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel Question
Ooops!
over 20 home runs Change the formula to: =IF(ROWS(D$1:D1)<=COUNTIF(runs,"="20),INDEX(playe r,SMALL(IF(runs=20,ROW(player)-MIN(ROW(player))+1),ROWS(D$1:D1))),"") -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... One way: A1:A20 = player B1:B20 = runs Enter this array formula** in D1 and copy down to D20: =IF(ROWS(D$1:D1)<=COUNTIF(runs,20),INDEX(player,SM ALL(IF(runs=20,ROW(player)-MIN(ROW(player))+1),ROWS(D$1:D1))),"") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "magnello27" wrote in message ... Here is my problem. Say I have a list of 20 baseball player names in one column and how many home runs that they have in the next column. How can I make a cell = all the players names that have over 20 home runs? Please help. Thank you. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel Question
wow!!!
-- RyGuy "T. Valko" wrote: Ooops! over 20 home runs Change the formula to: =IF(ROWS(D$1:D1)<=COUNTIF(runs,"="20),INDEX(playe r,SMALL(IF(runs=20,ROW(player)-MIN(ROW(player))+1),ROWS(D$1:D1))),"") -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... One way: A1:A20 = player B1:B20 = runs Enter this array formula** in D1 and copy down to D20: =IF(ROWS(D$1:D1)<=COUNTIF(runs,20),INDEX(player,SM ALL(IF(runs=20,ROW(player)-MIN(ROW(player))+1),ROWS(D$1:D1))),"") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "magnello27" wrote in message ... Here is my problem. Say I have a list of 20 baseball player names in one column and how many home runs that they have in the next column. How can I make a cell = all the players names that have over 20 home runs? Please help. Thank you. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel Question
wow!!!
It's nice to know how to do this but a filter is sooooo much easier *but* there are times when you may want *dynamic* capabilities. -- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... wow!!! -- RyGuy "T. Valko" wrote: Ooops! over 20 home runs Change the formula to: =IF(ROWS(D$1:D1)<=COUNTIF(runs,"="20),INDEX(playe r,SMALL(IF(runs=20,ROW(player)-MIN(ROW(player))+1),ROWS(D$1:D1))),"") -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... One way: A1:A20 = player B1:B20 = runs Enter this array formula** in D1 and copy down to D20: =IF(ROWS(D$1:D1)<=COUNTIF(runs,20),INDEX(player,SM ALL(IF(runs=20,ROW(player)-MIN(ROW(player))+1),ROWS(D$1:D1))),"") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "magnello27" wrote in message ... Here is my problem. Say I have a list of 20 baseball player names in one column and how many home runs that they have in the next column. How can I make a cell = all the players names that have over 20 home runs? Please help. Thank you. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Newbie Question - Subtraction Formula Question | Excel Discussion (Misc queries) | |||
| The question is an excel question that I need to figure out howto do in excel. | Excel Worksheet Functions | |||
| The above is an excel question | Excel Worksheet Functions | |||
| Excel question | Excel Discussion (Misc queries) | |||
| Statistical Excel Function Question within Excel 2000... | Excel Worksheet Functions | |||
Linear Mode
