Remember Me?

#21
May 18th 06, 08:15 AM posted to microsoft.public.excel.newusers
 RedHook Posts: n/a
League table automatic sort/update

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

#22
May 18th 06, 08:59 AM posted to microsoft.public.excel.newusers
 Bob Phillips Posts: n/a
League table automatic sort/update

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

First, hide zero values by going to ToolsoptionsView 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)

"RedHook" wrote in message
oups.com...
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

#23
May 18th 06, 02:15 PM posted to microsoft.public.excel.newusers
 Max Posts: n/a
League table automatic sort/update

"Bob Phillips" wrote:
Here is one way around it, bit convoluted but it works.
....

Thought it was simply dazzling, Bob !
Thanks
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
#24
May 18th 06, 02:44 PM posted to microsoft.public.excel.newusers
 Bob Phillips Posts: n/a
League table automatic sort/update

Dazzling, maybe, but it does work <G

Regards

Bob

"Max" wrote in message
...
"Bob Phillips" wrote:
Here is one way around it, bit convoluted but it works.
....

Thought it was simply dazzling, Bob !
Thanks
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post David Excel Discussion (Misc queries) 1 January 6th 06 03:56 AM travis Links and Linking in Excel 1 November 19th 05 02:30 PM rachael Excel Discussion (Misc queries) 11 September 19th 05 11:29 PM Adam Excel Discussion (Misc queries) 6 March 24th 05 02:35 PM Rachel Gonsior Excel Discussion (Misc queries) 3 March 21st 05 07:24 PM

All times are GMT +1. The time now is 11:41 PM.