Excel Question

  • Thread starter Thread starter magnello27
  • Start date Start date
M

magnello27

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.
 
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,SMALL(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)
 
Ooops!
over 20 home runs
Click to expand...

Change the formula to:

=IF(ROWS(D$1:D1)<=COUNTIF(runs,">="20),INDEX(player,SMALL(IF(runs>=20,ROW(player)-MIN(ROW(player))+1),ROWS(D$1:D1))),"")
 
wow!!!

--
RyGuy


T. Valko said:
Ooops!


Change the formula to:

=IF(ROWS(D$1:D1)<=COUNTIF(runs,">="20),INDEX(player,SMALL(IF(runs>=20,ROW(player)-MIN(ROW(player))+1),ROWS(D$1:D1))),"")
Click to expand...
 
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.
 
Back
Top