League table automatic sort/update

[RedHook]

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

 

[Bob Phillips]

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)

 

[Guest]

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

Thought it was simply dazzling, Bob !
Thanks

---

 

[Bob Phillips]

Dazzling, maybe, but it does work <G>

Regards

Bob