Calculation With Numbers Separated By Hyphen

[honkin]

I have a sheet which records football results. Without using a helper column, is it possible to calculate the difference in scores listed as such...1-2, 2-3, 0-0 and so on?

I need to be able to do the following:-

If the away team's score is 2 or more greater than the home team, it is a W, otherwise, an L. So 0-2 would be a win, but 0-0 or 0-1 would be a loss

P is the column where the scores are stored. I know the textsplit function can help to do things like SUM numbers separated by a hyphen =SUM(--(TEXTSPLIT(P2,"-") but how to do what I am after?

Any help gladly accepted

 

[myall_blues]

You don't have TEXTSPLIT available in your version of Excel. Try:

=IF(MID(P2,SEARCH("-",P2)+1,99)-LEFT(P2,SEARCH("-",P2)-1)>=2,"W","L")

 

[Peter_SSs]

.. or perhaps you do have the TEXTSPLIT function and need to update your profile to show your current version?

 

[honkin]

You don't have TEXTSPLIT available in your version of Excel. Try:

=IF(MID(P2,SEARCH("-",P2)+1,99)-LEFT(P2,SEARCH("-",P2)-1)>=2,"W","L")

Nailed it. It turned out Textsplit is not available in my MacOS version, but this works perfectly

Thanks so much

 

[myall_blues]

You’re welcome. Thanks for the feedback.

 

[honkin]

You’re welcome. Thanks for the feedback.

ah, one slight hitch
It worked on all results, except 0-1 and also 1-2. I just input the result from overnight, which was 0-1 and it put a L instead of a W.
So it worked on 1-4, 0-2, 1-0, 1-1....pretty much everything else. It seems anything with a single goal the difference to the away side fails.
It's OK, though. I experimented with it and changed the >=2 to >=1 and it now seems fine.
Cheers

 

[myall_blues]

Glad you got it sorted. Just noting though your original post says:

but 0-0 or 0-1 would be a loss

 

[honkin]

Glad you got it sorted. Just noting though your original post says:

You're spot on and just realised my error. I was looking at the column for away win, yet working in the column where it needed to be a win by 2 or more. You original code was fine and I changed it back. The false economy of working till midnight...my brain was fried
So with your same code, how would it be tweaked to have the second score be larger than the first score for a W and if not...a L?

 

[myall_blues]

Confirming - if the second (away) score is larger than the first (home) score by any amount, then W otherwise L?

=IF(MID(P2,SEARCH("-",P2)+1,99)>LEFT(P2,SEARCH("-",P2)-1),"W","L")

 

[honkin]

Confirming - if the second (away) score is larger than the first (home) score by any amount, then W otherwise L?

=IF(MID(P2,SEARCH("-",P2)+1,99)>LEFT(P2,SEARCH("-",P2)-1),"W","L")

Yes, away score larger than the first by any amount.
Thanks so much...you've been able to solve 2 issues in the blinking of an eye
Cheers