Help with an IF/AND formula please

dtdyson

New Member
Joined
Sep 29, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
HI All,
Using the current College World Series to have fun with some formulas. As you can see from the screenshot below, I'm trying to write a formula in cell H123 that will allow it to select the winning team based on the scores I enter in column G.
So, I've started with "if G120 is empty, leave H123 empty. What I'm then trying to say is (because it's a double elimination tourney) if Arizona beats Vandy in game 1 (so, if G126>G120) to still leave H123 blank but if Vandy beats Arizona (G120>G126) to put Vandy as the winner. Then, after the 2nd game, whomever has the highest score of that game is the winner.
Summary: if no scores entered, leave H123 blank. If top teams wins 1st game, enter top team name in H123. If bottom team wins first game, leave H123 blank. Whomever wins game 2, enter that team in H123.
When I test it the way it's written now, H123 remains blank if nothing is entered, and correctly puts Vandy if I have them winning. If Arizona wins the 1st game, H123 correctly remains blank too. But nothing happens when I put scores in the 2nd game.
1623706233306.png
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,297
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
See if this helps you see why:

VBA Code:
If G120 = "" Then
    H123 = ""
ElseIf G126 > G120 Then
    H123 = ""
ElseIf G120 > G126 Then
    H123 = F120
ElseIf G121 > G127 Then
    H123 = F120
Else
    H123 = F126
End If

I think your brackets are messed up, but that is neither here nor there.
 
Last edited:

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,297
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
D125 should be Stanford.

Delete the issue from my previous post and I think your formula would work as expected.
 
Last edited:

dtdyson

New Member
Joined
Sep 29, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
@johnnyL
Correct on D125 - I had left an older entry in there while I was playing with changing scores to see what would go into the next right hand cell. As the formula is currently written, Stanford does appear in D125 if I use the scores you see in the example. The issues are only with cell H123. H123 remains blank if no scores are entered in column G, and correctly puts Vandy if I have them winning the 1st game. If Arizona wins the 1st game, H123 correctly remains blank too. But nothing happens when I put scores in the 2nd game regardless of who I have winning so I'm assuming the issue with my formula has something to do with if(g126>g120,"" portion?
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,297
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows

ADVERTISEMENT

Your formula is working exactly the way you told it to. That is why I wrote it out for you in post #2 to help you see what your formula is doing. Delete the issue with your formula and it will do as you wanted.
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,297
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Sorry I missed your last sentence. Yes. That is what tells H123 to be blank. Delete that 'If' from your formula and H123 will be filled for you. You can delete the first 'If' portion also if you wanted to, there is no reason to tell a blank cell to stay blank.
 

dtdyson

New Member
Joined
Sep 29, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

So, I removed what I think is the portion you were referring to and the formula now looks like this:
=if(G120="","",if(G120>G126,F120,if(G121>G127,F120,F126)))
But, the same issue keeps happening - if (in this example) Arizona wins the first game (which means a 2nd game is required), it puts Arizona in H123. I want H123 to remain blank if Arizona wins game 1.
1623887209702.png
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,297
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
So, I removed what I think is the portion you were referring to and the formula now looks like this:
=if(G120="","",if(G120>G126,F120,if(G121>G127,F120,F126)))
But, the same issue keeps happening - if (in this example) Arizona wins the first game (which means a 2nd game is required), it puts Arizona in H123. I want H123 to remain blank if Arizona wins game 1.
View attachment 40953
Ahh, I see the additional error in our ways.

Try this:

=IF(G120="","",IF(G120>G126,F120,IF(G121>G127,F120,IF(NOT(ISBLANK(G121)),F126))))
 

dtdyson

New Member
Joined
Sep 29, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Ahh, I see the additional error in our ways.

Try this:

=IF(G120="","",IF(G120>G126,F120,IF(G121>G127,F120,IF(NOT(ISBLANK(G121)),F126))))
Oh man, that was close! You actually helped me find the right answer so thank you!

The formula as you wrote it results in "FALSE" showing in H123...
1624305475847.png


So, I used this and it now works as I want it:
=IF(G120="","",IF(G120>G126,F120,IF(AND(G126>G120,G121=""),"",(IF(G121>G127,F120,F126)))))

Now, if Vandy wins game 1 in my example, it correctly moves Vandy on:
1624306566260.png


If AZ wins Game 1, it correctly leaves H123 blank (using the AND statement):
1624306507160.png


And if game 2 is required, it correctly puts the winner:
1624306707752.png


I really appreciate your help on this!
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,297
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Glad you solved your dilemma.
 

Forum statistics

Threads
1,141,070
Messages
5,704,112
Members
421,327
Latest member
Msh

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