Need formula based on variable MAX of column

[baseball]

   A    B   C    D
1  Tm   W   L   PCT
-------------------
2  AA  12  10  .545
3  BB  13   9  .591
4  CC  14  11  .560

I want a formula in E2:E4 that is based on MAX(D2:D4).

In E2 it would be [(B3-B2)+(C2-C3)]/2, or [(13-12)+(10-9)]/2 = 2/2 =1

In E3 it would be [(B3-B3)+(C3-C3)]/2, or 0

In E4 it would be [(B3-B2)+(C2-C3)]/2, or [(13-14)+(11-9)]/2 = 1/2 ([ ] just used for clarity)

My problem is that the max value in column D will change rows as the data is updated. I know that I could sort the fields but I'm hoping to automate the whole procedure. I thought about OFFSET(MAX(D2:D4),r,c) but that does not work. Any suggestions?

 

[Robert Mika]

Hi basebal,
Should not be the last example:

[(B3-B4)+(C4-C3)]/2, or [(13-14)+(11-9)]/2 ?

and what do you mean by based on MAX?
Is the any standard form of your equation or the smaller number should be extracted from the bigger one

 

[baseball]

This is to find Games Behind in baseball standings. Typically, the team with the best winning percentage would be listed first and you would find the number of games behind the leader the other teams are by (Wmax-Wnext) + (Lnext-Lmax) and divide that by two. Generally the team with best winning percentage has the most wins and the fewest losses but when they have played an unequal number of games that isn't always true. From my original example, Team CC (14-11) trails Team BB (13-9) in winning percentage. I did forget to change the the cell references when I copied and pasted Team BB's record.

Anyway, what I mean "based on MAX" is that I was trying to use MAX(D2:D4) to find Wmax and Lmax. As I explained above, I cannot use MAX(B2:B4) and MIN(C2:C4) so I was trying to use MAX(D2:D4) to find the associated W and L values. I hope this makes sense (I'm trying to avoid having to sort six different groups of won-lost records every day.