League table automatic sort/update

  • Thread starter Thread starter RedHook
  • Start date Start date
Hi Bob, Max

Returning zero for a match that has still to be played is the desired
result. Thanks to both of you for your help on this it's much
appreciated. Just getting a feeling for how powerful some of these
Excel features really are.

Regards
RH
 
Here is one way around it, bit convoluted but it works.

First, hide zero values by going to Tools>options>View and unchecking the
Zero Values checkbox.

Then format the cells in results table like so

[<0.5]0;General

And finally, use this version of the formula

=SUMPRODUCT(--((($A$1:$A$20=$J2)*($B$1:$B$20>$C$1:$C$20))+(($D$1:$D$20=$J2)*
($B$1:$B$20<$C$1:$C$20))),--($E$1:$E$20=K$1))+

SUMPRODUCT(--((($A$1:$A$20=$J2)*($B$1:$B$20<$C$1:$C$20))+(($D$1:$D$20=$J2)*(
$B$1:$B$20>$C$1:$C$20))),--($E$1:$E$20=K$1))*0.0001+

SUMPRODUCT(--(($A$1:$A$20=$J2)+($D$1:$D$20=$J2)),--($E$1:$E$20=K$1),--($B$1:
$B$20=$C$1:$C$20))/2
--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)
 
Back
Top