# Help with an IF/AND formula please

#### dtdyson

##### New Member
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.

### Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

#### johnnyL

##### Well-known Member
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
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
@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
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
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
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.

#### johnnyL

##### Well-known Member
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
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...

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:

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

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

I really appreciate your help on this!

Replies
0
Views
1K
Replies
8
Views
434
Replies
3
Views
761
Replies
3
Views
525
Replies
2
Views
320

1,186,113
Messages
5,955,922
Members
438,225
Latest member
rsur

### 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