|
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. |
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. |
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. |
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. |
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. |
| All times are GMT +1. The time now is 02:00 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com