Hi guys,
For a score predictions system, i need a IF formula for this issue:
Man Utd 2-1 Arsenal IF Man Utd is in A2 and the first value (2) is bigger than the 2nd value (1), return H
Chelsea 1-0 Man Utd IF Man Utd is in C2 and the first value (1) is bigger than the 2nd value (0), return L
Liverpool 1-1 Man Utd IF the values are the same, return D
Spreadsheet attached for you to input formulas if you can. thank you!
Thanks,
R.
I understand also:
if ManU is on C2 and first value is smaller than 2nd is H (as in your fist condition, ManU won)
if Manu is on A2 and first value is smaller than 2nd is L (as in your second condition, ManU lose)
catch all possibilities with IF goes to be more complicated then.
Did you consider rearranging data (for ex. ManU always in col A (or col C) then it should be much easier to deal with.
Happy with my answer * Add Reputation.
If You are happy with solution, please use Thread tools and mark thread as SOLVED.
Hi there basically I need it to be Man Utd result (i.e. was a win, draw or loss predicted)
As fixtures are played home or away, I can't really swap them unless I do a copy and paste or something?
thank you for your suggestions so far
Last edited by AliGW; 08-05-2019 at 09:26 AM. Reason: Please don't quote unnecessarily!
Sure, rearranging was only as suggestion.
It can be solved even with this pattern.
OK great, can you please advise with the formulas I need please? I am at my wit's end!
Rayted - I am sure I've asked you not to quote unnecessarily before. Please would you stop doing so? It's just clutter.
Should H be W?
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
In order to avoid a complex nested IF function, you can create your own function i.e User Defined Function as per your requirement using VBA.
Place this UDF on a Standard Module like Module1 and to do that follow these steps...
1) Open your file and press Alt+F11 to open VB Editor.
2) On VB Editor's ribbon --> Insert --> Module and paste the following Function into the open code window.
3) Close the VB Editor and save your file as either Excel Macro-Enabled Workbook or Excel Binary Workbook or Excel 97-2003 Workbook.
Then you can use this Function on the Worksheet like below...
Please Login or Register to view this content.
In D1
Please Login or Register to view this content.
Regards
sktneer
Treat people the way you want to be treated. Talk to people the way you want to be talked to.
Respect is earned NOT given.
If it's of any use I think this might work?
Disclaimer - You would need to rework it (a longer formula) to accommodate the potential for double figure scores, but let's face it United won't be banging 10 past anyone this season!Please Login or Register to view this content.
Little tweak in D1 formula (still used sktneer UDF):
Formula:
Please Login or Register to view this content.
of course it UDF can be tweak a bit to give final result.
Last edited by KOKOSEK; 08-05-2019 at 09:40 AM.
Sorry Ali. I will ensure I don't do this in future
Thank you for this rep added, but I am avoiding macros.
this works thank you! and lol at your comment!
LOL! We've now gone from the sublime to the ridiculous!
You CAN quote if you are quoting to a post out of sequence (but only quote enough of the thread to make it clear to whom you are responding - it's enough usually to use @ and the respondee's name).
Just DON'T quote if you are responding to the post immediately before yours.
You're welcome rayted, happy to help.
The formula does have its 'disclaimered' limitation but no team has ever scored double figures in a game in the Prem. Although, and Ali probably doesn't want reminding of this, United did bang 9 past Ipswich!
Maybe?
=IF(COUNTIF(A1:C1,"Man Utd") =0,"",LOOKUP(SIGN(LEFT(B1,FIND("-",B1)-1)-REPLACE(B1,1,FIND("-",B1),"")),{-1,0,1},
IF(A1="Man Utd",{"L","D","H"},{"H","D","L"})))
Funny you should bring me into it! I'm not a Suffolk or a football girl: I was born in Derby, so I grew up around Rams and Forest, and Forest was always my preferred team of the two.
My other half, who is Fantasy-footy mad, is a Spurs fan.
Hi guys,
Just opening this thread again as I need to check something....
https://drive.google.com/file/d/1k0i...ew?usp=sharing
Here is my sheet. Looking at the 'main' tab, cell F5. Can anyone help me edit my formula so that if man utd are in cell C5 and their score is higher than the opposition, a W is returned in cell F5? Right now if i swap teams, I can't get the formula to update! Only when Man Utd ae in C5, the formula works.
Thank you!!!
Rayted - I won't follow links. Attach the workbook here, please.
Hi Ali
Sure. I wasn't sure if i should have put this in the google sheets thread.
Attaching the workbook now as downloaded from google sheets.
thank you!
Update: I am able to change the home team to man utd and when putting in a scoreline (draw) i.e. 1-1, i am getting the result: D (which is good) but i am not getting a W (when I am putting them as a home team)!
Really hope someone can help resolve this
Last edited by rayted; 08-10-2019 at 01:11 PM.
Is this for GoogleSheets, then? If so, I'll move it. Let me know.
Hi Ali,
I am using google sheets, but happy to have the formula dealt with in excel/as it is part of this thread.
thank you!
FIXED Now, never mind thanks!
Last edited by rayted; 08-10-2019 at 01:18 PM.
It's all very confusing. If you are looking for a solution for GoogleSheets you should post in the Other Platforms section. If it's for Excel, then post in the Excel section.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks