Conditional formatting for football pool winners

[macfuller]

I'm tracking the college football pools. I'd like to have conditional formatting to show the winner of each matchup. In the example I've chosen bold text and an orange border. But, I'm having a tough time figuring out how to write the conditional formatting logic to have it evaluate every pair of games.

Here is my current formatting code running from G5 to G56. Teams are in column G starting with G5, scores are in column H.

=IF(ISEVEN(ROW()),H6>OFFSET(H6,-1,0),H5>OFFSET(H5,1,0))

And here are the results - what's up with the Holiday Bowl? I appreciate your help.

CFP Bowl Pool.xlsx
	D	E	F	G	H
4	2021/2022 BOWLS			TEAMS	Actual FINAL SCORE
5	QUICK LANE			NEVADA	24
6	27-Dec	11AM	ESPN	W.MICHIGAN	52
7	MILITARY			BOSTON COLLEGE	ý
8	27-Dec	2:30PM	ESPN	E.CAROLINA	ý
9	BIRMINGHAM			AUBURN	40
10	28-Dec	NOON	ESPN	HOUSTON	20
11	FIRST RESPONDER			AIR FORCE	17
12	28-Dec	3:15PM	ESPN	LOUISVILLE	20
13	LIBERTY			MISS STATE	27
14	28-Dec	6:45PM	ESPN	TEXAS TECH	20
15	HOLIDAY			N.CAROLINA ST	18
16	28-Dec	8PM	FOX	UCLA	26
17	GUARANTEED RATE			MINNESOTA
18	28-Dec	10:15PM	ESPN	W.VIRGINIA
19	FENWAY			SMU	ý
20	29-Dec	11AM	ESPN	VIRGINIA	ý
21	PINSTRIPE			MARYLAND
22	29-Dec	2:15PM	ESPN	VIRGINIA TECH
23	CHEEZ-IT			CLEMSON
24	29-Dec	5:45PM	ESPN	IOWA STATE
25	ALAMO			OKLAHOMA
26	29-Dec	9:15PM	ESPN	OREGON
27	DUKE'S MAYO			N.CAROLINA
28	30-Dec	11:30AM	ESPN	S.CAROLINA
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G5:G56	Expression	=IF(ISEVEN(ROW()),H6>OFFSET(H6,-1,0),H5>OFFSET(H5,1,0))	text	NO

 

[Marcelo Branco]

Maybe...

Select G5:G56
Formula in CF
=AND(ISNUMBER($H5),$H5=MAX(OFFSET(INDEX(H$5:H$1000,MATCH(REPT("z",255),$F$5:$F6)),-1,0,2)))
pick a format

M

 

[macfuller]

Sorry, nothing gets formatted with that.

 

[Marcelo Branco]

Sorry, nothing gets formatted with that.

hmm...let's try another formula (worked perfectly for me)

Select G5:G100
Use this formula in CF
=AND(ISNUMBER($H5),$H5=MAX(OFFSET(INDEX($H$5:$H$100,2*(INT((ROWS($G$5:$G5)-1)/2)+1)-1),0,0,2)))
pick a format

M.

 

[Marcelo Branco]

Test

Pasta1
	D	E	F	G	H
4	2021/2022 BOWLS			TEAMS	Actual FINAL SCORE
5	QUICK LANE			NEVADA	24
6	27/12/2021	11AM	ESPN	W.MICHIGAN	52
7	MILITARY			BOSTON COLLEGE	ý
8	27/12/2021	2:30PM	ESPN	E.CAROLINA	ý
9	BIRMINGHAM			AUBURN	40
10	28/12/2021	NOON	ESPN	HOUSTON	20
11	FIRST RESPONDER			AIR FORCE	17
12	28/12/2021	3:15PM	ESPN	LOUISVILLE	20
13	LIBERTY			MISS STATE	27
14	28/12/2021	6:45PM	ESPN	TEXAS TECH	20
15	HOLIDAY			N.CAROLINA ST	18
16	28/12/2021	8PM	FOX	UCLA	26
17	GUARANTEED RATE			MINNESOTA
18	28/12/2021	10:15PM	ESPN	W.VIRGINIA
19	FENWAY			SMU	ý
20	29/12/2021	11AM	ESPN	VIRGINIA	ý
21	PINSTRIPE			MARYLAND
22	29/12/2021	2:15PM	ESPN	VIRGINIA TECH
23	CHEEZ-IT			CLEMSON
24	29/12/2021	5:45PM	ESPN	IOWA STATE
25	ALAMO			OKLAHOMA
26	29/12/2021	9:15PM	ESPN	OREGON
27	DUKE'S MAYO			N.CAROLINA
28	30/12/2021	11:30AM	ESPN	S.CAROLINA
Plan3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G5:G30	Expression	=AND(ISNUMBER($H5),$H5=MAX(OFFSET(INDEX($H$5:$H$100,2*(INT((ROWS($G$5:$G5)-1)/2)+1)-1),0,0,2)))	text	NO

M.

 

[macfuller]

That seems to have done the trick - thanks very much! What a convoluted solution to what seems like would be a common request... wasn't Excel designed for sports pools??

 

[Marcelo Branco]

You're welcome. Thanks for the feedback.

M.