Unsure how to calculate a formula with minus figures

[JrvsLa]

Hi all,

I'm very new to excel and I'm having a lot of trouble figuring out how to get a formula to calculate something for me. Any help would be greatly appreciated.

I have 2 columns of data:

Column 1 is Supremacy (team 1 v team 2) and column 2 is total points in the match. I then have 2 additional columns (team 1 expected points and team 2 expected points) in which I want to calculate a total for each team based on the total points value and the supremacy.

E.g. The supremacy is +1.55 and the Total is 2.55. The correct values for team 1 expected points would be calculated as 2.05 and for team 2 0.5 (the plus supremacy means team 1 is expected to score 1.55 points more than team 2, so 2.55-1.55=1 point left over which is then shared equally to 0.5 1.55+0.5 for team 1 and the remaining 0.5 for team 2). The problem I have is the supremacy value can also be a minus figure and this is when team 2 is expected to be the better team. E.g. the supremacy is -0.55 and the total is 2.15. I need excel to know this and then calculate accordingly. So in this example team 1 expected points should calculate as 0.8 and team 2 as 1.35.

Can anyone please help me with a formula that will calculate the 2 expected points column correctly?

Many thanks

 

[Tetra201]

Try this setup:

 

[FDibbins]

tetra, I think you forgot to add your "setup"?

 

[Tetra201]

tetra, I think you forgot to add your "setup"?

I did not. Just in case the image is not displayed:

Cell A1 --> 1.55 Cell B1 --> 2.55 Cell C1 --> =(B1+A1)/2 Cell D1 --> =(B1-A1)/2
Cell A2 --> -0.55 Cell B2 --> 2.15 Cell C2 --> =(B2+A2)/2 Cell D2 --> =(B2-A2)/2