Indicate Less Than cell value

gtd526

Active Member
Joined
Jul 30, 2013
Messages
477
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

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
1,693
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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

Active Member
Joined
Jul 30, 2013
Messages
477
Office Version
  1. 2019
Platform
  1. Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,426
Office Version
  1. 365
Platform
  1. Windows
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.
 
Solution

gtd526

Active Member
Joined
Jul 30, 2013
Messages
477
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,426
Office Version
  1. 365
Platform
  1. Windows
That's right. :)
Cell formatting doe not change what is in the cell, it just changes the way it's displayed.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,426
Office Version
  1. 365
Platform
  1. Windows
You're welcome.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,563
Messages
5,838,125
Members
430,529
Latest member
PaperBoi5870

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
Top