# Indicate Less Than cell value

#### gtd526

##### Well-known Member
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
ABCDE
1538377161
212/12Ind %Avg
3TeamH:HInj'sPtsTEST
4MIL 5170TRUE
5NYK 1226TRUE
6BKN 4350TRUE
7DET 3304TRUE
8DAL 3538FALSE
9OKC 00FALSE
10NO 30FALSE
11SAN 2345TRUE
12MIN 1399FALSE
13POR 4327TRUE
14ORL 5258TRUE
15LAL 4495FALSE
Test
Cell Formulas
RangeFormula
B1B1=Injuries!A5
C1C1=B1*0.7
D1D1=B1*0.3
A2A2=TODAY()
B4:B15B4=IF(\$H4="","",ROUND(SUMIFS(\$AT\$7:\$BN\$142,\$AP\$5:\$BJ\$140,\$H4),2))
C4:C15C4=IFERROR(INDEX(Injuries!\$2:\$2,MATCH(\$A\$4:\$A\$33,Injuries!\$1:\$1,0)),"")
D4:D15D4=IFERROR(INDEX(Injuries!\$B\$16:\$EQ\$16,MATCH(\$A4,Injuries!\$B\$1:\$EQ\$1,0)+4),"")
E4:E15E4=D4<\$C\$1

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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?

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
AKALAMANAO
1OKC
20Mins PlayedGames StartedFG%Pts
3
4
5
6
7
8
9
10
11
12
13
14
15
16Avg:0
17Total Pts:0
Injuries
Cell Formulas
RangeFormula
AK1AK1=LOOKUP(2, 1/((COUNTIF(\$B\$1:AK1,Favs!\$A\$4:\$A\$33)=0)*(Favs!\$A\$4:\$A\$33<>"")),Favs!\$A\$4:\$A\$33)
AK2AK2=COUNTIF(InjuryList!\$A:\$A,AK1)
AK3:AK15AK3=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:AL15AL3=IFERROR(VLOOKUP(AK3,Player_Totals_Table[[Player]:[MP]],7,0),"")
AM3:AM15AM3=IFERROR(VLOOKUP(AK3,Player_Totals_Table[[Player]:[GS]],6,0),"")
AN3:AN15AN3=IFERROR(VLOOKUP(AK3,Player_Totals_Table[[Player]:[FG%]],10,0),"")
AO3:AO15AO3=IFERROR(VLOOKUP(AK3,Player_Totals_Table[[Player]:[PTS]],29,0),"")
AO16AO16=IFERROR(AVERAGEIFS(AO3:AO15,AO3:AO15,">100"),"0")
AO17AO17=SUM(AO3:AO15)

NBA.xlsm
ABCDE
1538377161
212/12Ind %Avg
3TeamH:HInj'sPtsTEST
4MIL 5170TRUE
5NYK 1226TRUE
6BKN 4350TRUE
7DET 3304TRUE
8DAL 3538FALSE
9OKC 0 FALSE
10NO 30FALSE
11SAN 2345TRUE
12MIN 1399FALSE
13POR 4327TRUE
14ORL 5258TRUE
15LAL 4495FALSE
Test
Cell Formulas
RangeFormula
B1B1=Injuries!A5
C1C1=B1*0.7
D1D1=B1*0.3
A2A2=TODAY()
B4:B15B4=IF(\$H4="","",ROUND(SUMIFS(\$AT\$7:\$BN\$142,\$AP\$5:\$BJ\$140,\$H4),2))
C4:C15C4=IFERROR(INDEX(Injuries!\$2:\$2,MATCH(\$A\$4:\$A\$33,Injuries!\$1:\$1,0)),"")
D4:D15D4=IFERROR(INDEX(Injuries!\$B\$16:\$EQ\$16,MATCH(\$A4,Injuries!\$B\$1:\$EQ\$1,0)+4),"")
E4:E15E4=D4<\$C\$1

As 6String, suggested this formula is returning the 0 as text, not a number
Excel Formula:
``=IFERROR(AVERAGEIFS(AO3:AO15,AO3:AO15,">100"),"0")``
You need to remove the quotes from the 0.

As 6String, suggested this formula is returning the 0 as text, not a number
Excel Formula:
``=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.

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

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

You're welcome.

Replies
6
Views
446
Replies
6
Views
179
Replies
5
Views
269
Replies
0
Views
187
Replies
10
Views
488

1,216,126
Messages
6,129,005
Members
449,480
Latest member
yesitisasport

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back