IF statement

[Interesting Ian]

Hi,

I have a list of football matches. Home teams are in column E and away
teams are in column F. Each cell in Column H consists of text having "Match
Odds", "Correct Scores" or "Over/Under" in. Column J consists of a
suggested outcome of the match. When Column H says "Match Odds" column J
will either have the Home Team name down, the away team down, or simply say
"draw". In column K I want each cell to say either "HOME", "DRAW", "AWAY"
or to be left blank. If a cell in Column H *doesn't* say "Match Odds" I
want the corresponding cell in column K to be blank, where it does say
"Match Odds" and the cell in column J has the same team down as in column E
I want the corresponding cell in column K to say "HOME", if the cell in
column J has the same team down as in column F I want it to say "AWAY" and
if the cell in column J says "DRAW" then the corresponding cell in column K
should also be "DRAW".

I'll provide some examples to make it clear what I mean.

example 1
E3 = Aston Villa
F3 = Southampton
H3 = Match Odds
J3 = Aston Villa

Therefore since J3 = E3 then K3 = "HOME".

example 2
E25 = Blackburn
F25 = West Brom
H25 = Match Odds
J25 = DRAW

Therefore K25 should say DRAW

example 3
E51 = Bolton
F51 = Charlton
H51 = "Correct Score"
J51 = "1-2"

Because H51 doesn't say "Match Odds, I just want K51 to be left blank.

Hope someone can help me!

 

[Pete_UK]

Try this in K3:

=IF(J3=E3,"HOME",IF(J3=F3,"AWAY",IF(H3="Match Odds","DRAW","")))

Hope this helps.

Pete

 

[Interesting Ian]

Try this in K3:

=IF(J3=E3,"HOME",IF(J3=F3,"AWAY",IF(H3="Match Odds","DRAW","")))

Hope this helps.

Pete

 

[Pete_UK]

Do you mean that column J cells are blank and column H contains Match
Odds? If so, amend the formula to this:

=IF(J3=E3,"HOME",IF(J3=F3,"AWAY",IF(AND(H3="Match
Odds",J3<>""),"DRAW","")))

Hope this helps.

Pete

 

[Interesting Ian]

Hi,

When H says "Match Odds" J has either home team (eg "Aston Villa"), away
team (eg "Southampton") or simply says "DRAW". Just getting the exact same
result with that formula I'm afraid . i.e it says "DRAW" all the time when
H column says "Match Odds". However it correctly just gives a blank when H
says something other than "Match Odds".

Do you mean that column J cells are blank and column H contains Match
Odds? If so, amend the formula to this:

=IF(J3=E3,"HOME",IF(J3=F3,"AWAY",IF(AND(H3="Match
Odds",J3<>""),"DRAW","")))

Hope this helps.

Pete

 

[Pete_UK]

Are you sure that Aston Villa in E3 is exactly the same as the Aston
Villa in J3? i.e. check that there are no extra spaces in one or the
other cell. If you enter =LEN(E3) in a blank cell somewhere and =LEN
(J3) in another blank cell, do you get the same numbers? You could
also put =E3=J3 in another cell and you will get TRUE if they are both
the same and FALSE if they are different.

Pete

 

[Interesting Ian]

Hi Pete,

No they weren't. Anyway I have got it working now. Thanks for all your
help


Are you sure that Aston Villa in E3 is exactly the same as the Aston
Villa in J3? i.e. check that there are no extra spaces in one or the
other cell. If you enter =LEN(E3) in a blank cell somewhere and =LEN
(J3) in another blank cell, do you get the same numbers? You could
also put =E3=J3 in another cell and you will get TRUE if they are both
the same and FALSE if they are different.

Pete

 

[Pete_UK]

You're welcome, Ian.

Perhaps you can share with us what was wrong and how you fixed it.

Pete

 

[Interesting Ian]

I asked on this discussion board where some people actually looked at my
spreadsheet. Someone suggested I put in =IF(H3<>"Match
odds","",IF(AND(H3="match
odds",TRIM(J3)=TRIM(E3)),"HOME",IF(TRIM(J3)=TRIM(F3),"AWAY",IF(J3="The
Draw","DRAW",E3))))

And this seems to work.




You're welcome, Ian.

Perhaps you can share with us what was wrong and how you fixed it.

Pete

 

[Pete_UK]

Okay, thanks for feeding back.

The TRIM functions imply that you could have had spurious spaces.

Strictly speaking, you dont need:

AND(H3="match odds",TRIM(J3)=TRIM(E3))

as you have alredy tested for H3<>"Match odds", so you could have:

=IF(H3<>"Match odds","",IF(TRIM(J3)=TRIM(E3),"HOME",IF(TRIM(J3)=TRIM
(F3),"AWAY",IF(J3="The Draw","DRAW",E3))))

Hope this helps.

Pete

 

[Interesting Ian]

I'm just wondering why I have these spurious spaces. I used the text to
column function. Might that create them? Otherwise it'll have been like
that in the original spreadsheet I downloaded (Betfair historical data).


Okay, thanks for feeding back.

The TRIM functions imply that you could have had spurious spaces.

Strictly speaking, you dont need:

AND(H3="match odds",TRIM(J3)=TRIM(E3))

as you have alredy tested for H3<>"Match odds", so you could have:

=IF(H3<>"Match odds","",IF(TRIM(J3)=TRIM(E3),"HOME",IF(TRIM(J3)=TRIM
(F3),"AWAY",IF(J3="The Draw","DRAW",E3))))

Hope this helps.

Pete