search and answer only ODD rows

[gtd526]

Hello,
I want to only use ODD rows for the search and the answer.
L4:L13 contains the formula I want to change.
If I:I is >=55%, then return A:A value, only in ODD rows.

thank you

NBA.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
2												LOOK AT ODD ROWS ONLY
3									Target			FORMULA >= I:I >.55			should only be
4	DAL	4.5	15	57.7%	52.6%	-0.1	1.2		62%			DAL	even		ORL
5	CLE	-4.5	14	64.3%	51.9%	2.8	5.3	0.8	48%			ORL			MIN
6	BRK	9.5	24	33.3%	46.3%	-0.7	-2.5		39%			GS	even		OKC
7	ORL	-9.5	5	72.0%	63.8%	1.5	0.7	-8.8	68%			PHI	even
8	GS	-11.5	3	52.0%	54.6%	-0.2	1.7	-9.8	68%			NOP	even
9	WAS	11.5	30	11.1%	48.2%	-1.5	-9.8		33%			MIN
10	UTA	1.5	27	31.0%	54.4%	0.1	-2.4		43%			OKC
11	ATL	-1.5	23	46.7%	31.6%	-3.2	-2.2		30%			MIA	even
12	PHI	11.5	10	57.7%	56.1%	0.5	3.7		58%			0
13	BOS	-11.5	1	89.7%	50.0%	1.9	10.3	-1.2	52%			0
14	NOP	-2.5	5	58.6%	54.4%	2.2	4.1	1.6	56%			0
15	NYK	2.5	7	69.0%	53.6%	2.3	4.3		54%
16	SAS	12.5	28	18.8%	46.6%	-1.6	-8.7		47%
17	MIN	-12.5	3	76.9%	54.6%	2.6	7.2	-5.3	58%
18	DET	10.5	25	13.8%	50.0%	-0.4	-9.0		52%
19	CHI	-10.5	20	53.6%	53.6%	0.0	-1.3		52%
20	CHA	14.5	30	25.0%	40.4%	-2.1	-10.0		36%
21	MIL	-14.5	8	79.3%	40.4%	-2.5	3.5	-11.0	43%
22	HOU	10	23	18.5%	50.9%	2.0	0.0		35%
23	OKC	-10	2	79.3%	64.3%	4.5	8.1	-1.9	69%
24	MIA	-6.5	11	58.6%	50.0%	0.6	0.6	-5.9	57%
25	POR	6.5	27	32.1%	44.6%	-1.4	-9.0		39%
26
27
28
29
30
31
32
33
Sheet1

Cell Formulas
Range	Formula
C4:C33	C4	=IFERROR(IF(MOD(ROW(),2)=0,ROUND(VLOOKUP($A4,'TeamRankings - Stats'!$EU$3:$EY$32,5,0),2),ROUND(VLOOKUP($A4,'TeamRankings - Stats'!$EU$3:$EX$32,4,0),2)),"")
D4:D33	D4	=IFERROR(IF(MOD(ROW(),2)=0,VLOOKUP($A4,'TeamRankings - Stats'!$EK$3:$ER$32,8,0),VLOOKUP($A4,'TeamRankings - Stats'!$EK$3:$EQ$32,7,0)),"")
E4:E33	E4	=IFERROR(VLOOKUP(A4,'ATS Trends'!$O$3:$R$32,4,0),"")
F4:F33	F4	=IFERROR(VLOOKUP($A4,'ATS Trends'!$O$3:$T$32,6,0),"")
G4:G33	G4	=IFERROR(VLOOKUP($A4,'ATS Trends'!$O$3:$S$32,5,0),"")
H4:H33	H4	=IF(AND($B4<0,$G4=""),"",IF(AND($A4=$AI4,$B4<0,$G4<0),$B4+$G4,IF(AND($B4<0,$G4>0),$G4-ABS($B4),"")))
I4:I33	I4	=IFERROR(IF(MOD(ROW(),2)=0,ROUND(VLOOKUP($A4,'ATS Trends'!$H$3:$K$32,4,0),2),ROUND(VLOOKUP($A4,'ATS Trends'!$A$3:$D$32,4,0),2)),"")
B5,B33,B31,B29,B27,B25,B23,B21,B19,B17,B15,B13,B11,B9,B7	B5	=IF(ISTEXT(A4),IF(B4="PK","PK",IF(B4<0,B4*-1,B4*-1)),"")
L4:L14	L4	=INDEX($A$4:$A$33,SMALL(IF($I$4:$I$33>=0.55*($I$4:$I$33<>""),ROW($I$4:$I$33)-ROW($I$4)+1),ROW(1:1)))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[StephenCrump]

Try: =IFERROR(INDEX($A$4:$A$33,SMALL(IF(($I$4:$I$33>=0.55)*($I$4:$I$33<>"")*ISODD(ROW($I$4:$I$33)),ROW($I$4:$I$33)-ROW($I$4)+1),ROWS(L$4:L4))),"")

This will return odd rows, so if you insert a new row at the top of the worksheet, the formula will return DAL, GS, PHI etc, as these are now in the odd rows.

If you still want the formula to return ORL, MIN and OKC, just change to:

=IFERROR(INDEX($A$4:$A$33,SMALL(IF(($I$4:$I$33>=0.55)*($I$4:$I$33<>"")*ISODD(ROW($I$4:$I$33)-ROW(I$4)),ROW($I$4:$I$33)-ROW($I$4)+1),ROWS(L$4:L4))),"")

 

[SunnyAlv]

Try

=INDEX(A1:A25,FILTER(FILTER(ROW(A4:A25),I4:I25>55%),MOD(FILTER(ROW(A4:A25),I4:I25>55%),2)=1))

 

[kevin9999]

Or perhaps:

=FILTER(A4:A25,(ISODD(ROWS(A4:A25))*(I4:I25>=0.55)),"")

 

[Peter_SSs]

@gtd526
Didn't we basically already cover this at the end of your earlier thread? (apart from incrementing the final argument to return subsequent values)
Adapted to this thread it would have been ..

=IFERROR(INDEX(A$4:A$33,AGGREGATE(15,6,(ROW(H$4:H$33)-ROW(H$4)+1)/((I$4:I$33>=0.55)*(H$4:H$33<>"")*ISODD(ROW(H$4:H$33))),ROWS(L$4:L4))),"")

In that earlier thread the issue of possible inserted rows as mentioned by Stephen above was also raised.


@SunnyAlv @kevin9999
I think that you both have missed that the OP is referencing the range down to row 33 and column I has some formulas returning "" in that range.
Kevin, I also think that you didn't test your formula (ROWS v ROW) but you are on the right track in that SunnyAlv has a much more complicated formula than is needed.
If FILTER is available then

=FILTER(A4:A33,(I4:I33>55%)*(I4:I33<>"")*ISODD(ROW(A4:A33)))

 

[gtd526]

Try: =IFERROR(INDEX($A$4:$A$33,SMALL(IF(($I$4:$I$33>=0.55)*($I$4:$I$33<>"")*ISODD(ROW($I$4:$I$33)),ROW($I$4:$I$33)-ROW($I$4)+1),ROWS(L$4:L4))),"")

This will return odd rows, so if you insert a new row at the top of the worksheet, the formula will return DAL, GS, PHI etc, as these are now in the odd rows.

If you still want the formula to return ORL, MIN and OKC, just change to:

=IFERROR(INDEX($A$4:$A$33,SMALL(IF(($I$4:$I$33>=0.55)*($I$4:$I$33<>"")*ISODD(ROW($I$4:$I$33)-ROW(I$4)),ROW($I$4:$I$33)-ROW($I$4)+1),ROWS(L$4:L4))),"")

Both work.
Thank you.

 

[gtd526]

@gtd526
Didn't we basically already cover this at the end of your earlier thread? (apart from incrementing the final argument to return subsequent values)
Adapted to this thread it would have been ..

=IFERROR(INDEX(A$4:A$33,AGGREGATE(15,6,(ROW(H$4:H$33)-ROW(H$4)+1)/((I$4:I$33>=0.55)*(H$4:H$33<>"")*ISODD(ROW(H$4:H$33))),ROWS(L$4:L4))),"")

In that earlier thread the issue of possible inserted rows as mentioned by Stephen above was also raised.


@SunnyAlv @kevin9999
I think that you both have missed that the OP is referencing the range down to row 33 and column I has some formulas returning "" in that range.
Kevin, I also think that you didn't test your formula (ROWS v ROW) but you are on the right track in that SunnyAlv has a much more complicated formula than is needed.
If FILTER is available then

=FILTER(A4:A33,(I4:I33>55%)*(I4:I33<>"")*ISODD(ROW(A4:A33)))

Your formula works, thanks.
This was a little different than my earlier post, so I decided to post a new question.