Indicate Less Than cell value

[gtd526]

Hello,
Using the index,match formula (D:D), when it returns 0, it's not indicating Less Than 377(C1). How can I get it to indicate Less Than, if true?
Thank you.

NBA.xlsm
	A	B	C	D	E
1		538	377	161
2	12/12	Ind %		Avg
3	Team	H:H	Inj's	Pts	TEST
4	MIL		5	170	TRUE
5	NYK		1	226	TRUE
6	BKN		4	350	TRUE
7	DET		3	304	TRUE
8	DAL		3	538	FALSE
9	OKC		0	0	FALSE
10	NO		3	0	FALSE
11	SAN		2	345	TRUE
12	MIN		1	399	FALSE
13	POR		4	327	TRUE
14	ORL		5	258	TRUE
15	LAL		4	495	FALSE
Test

Cell Formulas
Range	Formula
B1	B1	=Injuries!A5
C1	C1	=B1*0.7
D1	D1	=B1*0.3
A2	A2	=TODAY()
B4:B15	B4	=IF($H4="","",ROUND(SUMIFS($AT$7:$BN$142,$AP$5:$BJ$140,$H4),2))
C4:C15	C4	=IFERROR(INDEX(Injuries!$2:$2,MATCH($A$4:$A$33,Injuries!$1:$1,0)),"")
D4:D15	D4	=IFERROR(INDEX(Injuries!$B$16:$EQ$16,MATCH($A4,Injuries!$B$1:$EQ$1,0)+4),"")
E4:E15	E4	=D4<$C$1

 

[6StringJazzer]

This cannot be answered definitively without seeing the data on sheet Injuries, specifically Injuries!$B$16:$EQ$16. I suspect that the "0" in D9 and D10 is not the number 0 but is text. Are there formulas in Injuries!$B$16:$EQ$16?

 

[gtd526]

This cannot be answered definitively without seeing the data on sheet Injuries, specifically Injuries!$B$16:$EQ$16. I suspect that the "0" in D9 and D10 is not the number 0 but is text. Are there formulas in Injuries!$B$16:$EQ$16?

Yes. The formula is the same for each team. I formatted the cells used as "number". The IFERROR in the formula has been changed from "0" to "" for one team and not the other.

NBA.xlsm
	AK	AL	AM	AN	AO
1	OKC
2	0	Mins Played	Games Started	FG%	Pts
3
4
5
6
7
8
9
10
11
12
13
14
15
16				Avg:	0
17				Total Pts:	0
Injuries

Cell Formulas
Range	Formula
AK1	AK1	=LOOKUP(2, 1/((COUNTIF($B$1:AK1,Favs!$A$4:$A$33)=0)*(Favs!$A$4:$A$33<>"")),Favs!$A$4:$A$33)
AK2	AK2	=COUNTIF(InjuryList!$A:$A,AK1)
AK3:AK15	AK3	=IFERROR(INDEX(InjuryList!$B$3:$B$227,AGGREGATE(15,6,(ROW(InjuryList!$A$3:$A$202)-ROW(InjuryList!$A$2))/(AK$1=InjuryList!$A$3:$A$202),ROWS(AK$3:AK3))),"")
AL3:AL15	AL3	=IFERROR(VLOOKUP(AK3,Player_Totals_Table[[Player]:[MP]],7,0),"")
AM3:AM15	AM3	=IFERROR(VLOOKUP(AK3,Player_Totals_Table[[Player]:[GS]],6,0),"")
AN3:AN15	AN3	=IFERROR(VLOOKUP(AK3,Player_Totals_Table[[Player]:[FG%]],10,0),"")
AO3:AO15	AO3	=IFERROR(VLOOKUP(AK3,Player_Totals_Table[[Player]:[PTS]],29,0),"")
AO16	AO16	=IFERROR(AVERAGEIFS(AO3:AO15,AO3:AO15,">100"),"0")
AO17	AO17	=SUM(AO3:AO15)

NBA.xlsm
	A	B	C	D	E
1		538	377	161
2	12/12	Ind %		Avg
3	Team	H:H	Inj's	Pts	TEST
4	MIL		5	170	TRUE
5	NYK		1	226	TRUE
6	BKN		4	350	TRUE
7	DET		3	304	TRUE
8	DAL		3	538	FALSE
9	OKC		0		FALSE
10	NO		3	0	FALSE
11	SAN		2	345	TRUE
12	MIN		1	399	FALSE
13	POR		4	327	TRUE
14	ORL		5	258	TRUE
15	LAL		4	495	FALSE
Test

Cell Formulas
Range	Formula
B1	B1	=Injuries!A5
C1	C1	=B1*0.7
D1	D1	=B1*0.3
A2	A2	=TODAY()
B4:B15	B4	=IF($H4="","",ROUND(SUMIFS($AT$7:$BN$142,$AP$5:$BJ$140,$H4),2))
C4:C15	C4	=IFERROR(INDEX(Injuries!$2:$2,MATCH($A$4:$A$33,Injuries!$1:$1,0)),"")
D4:D15	D4	=IFERROR(INDEX(Injuries!$B$16:$EQ$16,MATCH($A4,Injuries!$B$1:$EQ$1,0)+4),"")
E4:E15	E4	=D4<$C$1

 

[Fluff]

As 6String, suggested this formula is returning the 0 as text, not a number

=IFERROR(AVERAGEIFS(AO3:AO15,AO3:AO15,">100"),"0")

You need to remove the quotes from the 0.

 

[gtd526]

As 6String, suggested this formula is returning the 0 as text, not a number

=IFERROR(AVERAGEIFS(AO3:AO15,AO3:AO15,">100"),"0")

You need to remove the quotes from the 0.

Thank you. Anything within quotes are treated as Text, without quotes are Numbers, correct? Regardless of the formatting of the cell.

 

[Fluff]

That's right.
Cell formatting doe not change what is in the cell, it just changes the way it's displayed.

 

[gtd526]

That's right.
Cell formatting doe not change what is in the cell, it just changes the way it's displayed.

Thank you.

 

[Fluff]

You're welcome.