Here is a challenge for everyone. I am looking for a list of players that are scoring below 10 pts OR shooting less than 41% from the field. Also, in a separate chart, I am looking for the top 3 scorers from each league.
ex. Basketball Stats
Also, I am attaching a Google Docs link so everyone can try. It will be a great help if someone was able to figure this out. Thanks.
With traditional Array formulas you can use the following:
in cell F3 (this is an array formula and must be confirmed with Ctrl+Shift+Enter)
=IFERROR(INDEX(A$2:A$31,SMALL(IF(($C$2:$C$31<10)+($D$2:$D$31<41),ROW($A$1:$A$30)),ROW(A1))),"")
Copy across to column I and down to row 31.
In cell K3 (this is an array formula and must be confirmed with Ctrl+Shift+Enter)
=INDEX($A$2:$A$31,MATCH(K$2&LARGE(IF($B$2:$B$31=K$2,$C$2:$C$31),ROW(A1)),INDEX($B$2:$B$31&$C$2:$C$31,0),0))
Copy across to M3 and down to the following two rows.
** Edit: ** If there are different conditions for each league, you can work these into the IF() statement. In an Array formula you cannot use the AND() or OR() formulas to combine conditions. Instead, each condition will be put inside brackets and combined with * for AND and + for OR.
The logic in words is
((League=A) AND ((PTS<10) OR (FG<41))) OR
((League=B) AND ((PTS<15) OR (FG<50))) OR
((League=C) AND ((PTS<15) OR (FG<50)))
In the formula that would then look like
(($B$2:$B$31="A")*(($C$2:$C$31<10)+($D$2:$D$31<41)))+
(($B$2:$B$31="B")*(($C$2:$C$31<15)+($D$2:$D$31<50)))+
(($B$2:$B$31="C")*(($C$2:$C$31<15)+($D$2:$D$31<50)))
The complete formula is then (remember Ctrl-Shift-Enter)
=IFERROR(INDEX(A$2:A$31,SMALL(IF(
(($B$2:$B$31="A")*(($C$2:$C$31<10)+($D$2:$D$31<41)))+
(($B$2:$B$31="B")*(($C$2:$C$31<15)+($D$2:$D$31<50)))+
(($B$2:$B$31="C")*(($C$2:$C$31<15)+($D$2:$D$31<50))),
ROW($A$1:$A$30)),ROW(A1))),"")
The same condition logic and architecture would apply to the Dynamic Array Filter() function in my other answer.
This is a prime example for the new Excel Dynamic Array functions, which are currently available only in Office Insider builds.
One (ONE!) formula in cell F3 goes like this:
=FILTER($A$2:$D$31,($C$2:$C$31<10)+($D$2:$D$31<41))
It automatically extends to the right and down.
Another formula in cell K3. I changed the cells K2 to M2 to have the same text as column B.
=INDEX(SORTBY(FILTER($A$2:$A$31,$B$2:$B$31=K2),FILTER($C$2:$C$31,$B$2:$B$31=K2),-1),{1;2;3})
This has been copied to L3 and M3, but nothing has been copied down.
(note: if your system uses semicolons in Excel formulas, you need to swap the commas for semicolons and the semicolons for commas)
