[Solved] IF(AND formula function in sport results?

[Topshot]

Hi guys,

I'm trying to write a formula expressing the results of sport matches.
The result format for each match is (R); Win = 3; Draw = 1; Lose = 0;
Each match has a pre-home win rating (H), a pre-draw rating (D) , and a pre-away win rating (A).

E.g; Home win 40; Draw 20; Away win 10
If the home team wins, it gets 40, if it draws it gets 20.
If the home team loses, I want it to get the AWAY win rating (10), because it has under-performed, and I want the away team to get the HOME win rating (40) because it has over-performed .

This is the formula I've started for the home team, but do not know how to finish;

=IF(R2=3;H2;IF(AND(R2=0);A2;IF(AND(R2=1);D2)))

If the home team has the higher rating and it wins, then everything is OK.
If the away team has the higher rating and it wins, then everything is OK.

If the home team has the higher rating and it loses, I don't know how to write this, how to swap the (H) and (A) ratings around.
If the away team has the higher rating and it loses, I don't know how to write this, how to swap the (H) and (A) ratings around.

Any help will be absolutely appreciated.

[Weatherlawyer]

What sort of an idiot would write a link to the guide that tells you to log in to edit the help page?

Probably the same sort of idiot that would write a search parameter that precludes IF from the search.

Sometimes I wonder if I aught to pay the tax and get another Office. Anyone know the BB code for shakes head in disgust?

[Hagar Delest]

What sort of an idiot would write a link to the guide that tells you to log in to edit the help page?

Probably the same sort of idiot that would write a search parameter that precludes IF from the search.

Sometimes I wonder if I aught to pay the tax and get another Office. Anyone know the BB code for shakes head in disgust?

I just tested the warn user feature of this forum on Weatherlawyer for that post. Topshot, please disregard it, according to the other posts from Weatherlawyer, he's not in a position to give others lessons. Thanks to avoid replying that post.

[Weatherlawyer]

I just tested the warn user feature of this forum on Weatherlawyer for that post. Topshot, please disregard it, according to the other posts from Weatherlawyer, he's not in a position to give others lessons. Thanks to avoid replying that post.

Never mind me being an arsehole get it sorted.

[Hagar Delest]

Topshot, could you please upload a sample file? I can't see how your data are organized. What's clear is that your AND function has absolutely no effect. An AND function is build like that: AND(condition_1;condition_2;...;condition_n). In an IF test, it will give: IF(AND(condition_1;condition_2;...;condition_n);Value_if_true;Value_if_false).

Never mind me being an arsehole

I do because I don't want users like Topshot to be disgusted by this forum because of users like you.

[Topshot]

Thank you Hagar for your time. I have had to compile the results like this, as I am not familiar yet with uploading spreadsheet files. I hope you can see the theory of this.
.
.
Team ?...Venue...(R)esult....Rating.....Team ?.....Venue....(R)esult.....Rating.....(H)ome...(D)raw...(A)way
. .
Team A.. Home........0........(20).......Team B....Away.........3..........(52)..........52..........29........20
Team C.. Home........3........(43).......Team D....Away.........0.........(30)...........43..........29........30
Team E.. Home........1........(30).......Team F....Away.........1..........(30)..........39..........30........32
.
Team A loses to Team B
Team A with a rating of 52 is expected to win at home, but it doesn't. So I want to give it the rating of Team B (20)
Team B with a rating of 20 is not expected to win away, but it does. So I want to give it the rating of Team A (52)

Team C beats Team D
Team C with a rating of 43 is expected to win at home, and it does. So I want to give it the rating of Team C (43)
Team D with a rating of 30 is not expected to win away, and it doesn't. So I want to give it the rating of Team D (20)

Teams E and F draw
Team E with a rating of 39 is expected to win at home, but it draws. So I want to give it the rating of Draw (30)
Team F with a rating of 32 is not expected to win away, but it draws. So I want to give it the rating of Draw (30)

[Hagar Delest]

In fact, you don't need any AND function at all.

You give the winner the maximum rating in fact: if it's home team, it gives its own rating, if it's away team, it's the other team rating, but in all cases, it's the maximum of both team ratings. Same for the loser.

So basically, the formula is:
IF(R=1;Draw_Value;IF(R=3;MAX(Rating_1;Rating_2);MIN(Rating_1;Rating_2)))
IF(R=1;Draw_Value: draw case, just give the draw rating
IF(R=3;MAX(Rating_1;Rating_2): win case, give the max rating
MIN(Rating_1;Rating_2): if result is neither draw nor win, then, it's lose so give the lowest rating.

See attached file. Note that I've improved the 2nd result field, it is automatically calculated depending on the first result field. I've also made the same table but with the CHOOSE function, which is better IMHO, it avoids having nested IF functions.

Thanks to add '[Solved]' in your first post title (edit button) if your issue has been fixed.

[Topshot]

Thanks Hagar for being so quick, correct and flexible with the options. Once again a big thank you.