# Excel 2007 : League table - how to avoid blank matches being read as draws?

1. ## League table - how to avoid blank matches being read as draws?

I have a league table spreadsheet, which uses the scores from the fixtures to automatically update it.
It uses SUMPRODUCT to identify whether the matches ended up a win/draw/loss, as well as the goals scored/conceded.
However, one flaw is that any matches currently unplayed (blank) get read as draws. This obviously stops the table working, so if anyone knows how to stop the formula reading the empty cell matches it'd be appreciated.

The formula used in the draws column is:-
=SUMPRODUCT((\$A\$2:\$A\$65=J2)*(\$E\$2:\$E\$65=\$F\$2:\$F\$65))

It was suggested to use the IF LEN function and I tried multiple ways, again without success. So I think this will work if I get the formula right.

2. ## Re: League table - how to avoid blank matches being read as draws?

=SUMPRODUCT((\$A\$2:\$A\$65=J2)*(\$E\$2:\$E\$65=\$F\$2:\$F\$65)*ISNUMBER(\$E\$2:\$E\$65))

or

=SUMPRODUCT((\$A\$2:\$A\$65=J2)*(\$E\$2:\$E\$65=\$F\$2:\$F\$65)*(\$E\$2:\$E\$65&\$F\$2:\$F\$65<>""))

3. ## Re: League table - how to avoid blank matches being read as draws?

thanks, the first one worked a treat!

4. ## Re: League table - how to avoid blank matches being read as draws?

Hi

This thread might be of interest to you

Sorting a football/soccer league table

http://www.excelforum.com/excel-gene...gue-table.html

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

## X vBulletin 4.1.8 Debug Information

• Page Generation 0.13291 seconds
• Memory Usage 8,905KB
• Queries Executed 14 (?)
Template Usage (31):
• (1)footer
• (1)forumrules
• (1)gobutton
• (4)memberaction_dropdown
• (1)navbar
• (4)navbar_noticebit
• (6)option
• (4)postbit_legacy
• (4)postbit_onlinestatus
• (4)postbit_wrapper
• (1)spacer_close
• (1)spacer_open
• (1)tagbit_wrapper

Phrase Groups Available (6):
• global
• inlinemod
• postbit
• posting
• reputationlevel
Included Files (39):
• ./vbseo.php
• ./env.php
• ./vbseo/includes/functions_vbseo.php
• ./vbseo/includes/functions_vbseo_pre.php
• ./vbseo/includes/functions_vbseo_url.php
• ./vbseo/includes/functions_vbseo_createurl.php
• ./vbseo/includes/functions_vbseo_db.php
• ./vbseo/includes/functions_vbseo_vb.php
• ./vbseo/includes/functions_vbseo_seo.php
• ./vbseo/includes/functions_vbseo_misc.php
• ./vbseo/includes/functions_vbseo_crr.php
• ./vbseo/includes/functions_vbseo_cache.php
• ./vbseo/includes/functions_vbseo_hook.php
• ./vbseo/includes/functions_vbseo_startup.php
• ./includes/config.php
• ./global.php
• ./includes/class_bootstrap.php
• ./includes/init.php
• ./includes/class_core.php
• ./includes/functions.php
• ./includes/class_friendly_url.php
• ./includes/class_hook.php
• ./includes/functions_cforum.php
• ./includes/functions_bigthree.php
• ./includes/class_postbit.php
• ./includes/class_bbcode.php
• ./includes/functions_reputation.php
• ./includes/class_bootstrap_framework.php
• ./vb/vb.php
• ./vb/phrase.php
• ./includes/functions_notice.php
• ./packages/vbattach/attach.php
• ./vb/types.php
• ./vb/cache.php
• ./vb/cache/db.php
• ./vb/cache/observer/db.php
• ./vb/cache/observer.php

Hooks Called (46):
• init_startup
• friendlyurl_resolve_class
• database_pre_fetch_array
• database_post_fetch_array
• global_bootstrap_init_start
• global_bootstrap_init_complete
• cache_permissions
• fetch_foruminfo
• global_state_check
• global_bootstrap_complete
• global_start
• style_fetch
• global_setup_complete
• friendlyurl_redirect_canonical
• bbcode_fetch_tags
• bbcode_create
• postbit_factory
• postbit_display_start
• cache_templates
• template_register_var
• parse_templates
• notices_check_start
• notices_noticebit
• process_templates_complete
• reputation_image
• postbit_imicons
• bbcode_parse_start
• bbcode_parse_complete_precache
• bbcode_parse_complete
• postbit_display_complete
• memberaction_dropdown
• tag_fetchbit_complete
• forumrules