Indicate Less Than cell value

gtd526

Well-known Member
Joined
Jul 30, 2013
Messages
660
Office Version
  1. 2019
Platform
  1. Windows
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?
 
Upvote 0
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
 
Upvote 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.
 
Upvote 0
Solution
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.
 
Upvote 0
That's right. :)
Cell formatting doe not change what is in the cell, it just changes the way it's displayed.
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top