Game score without game being played

[Sheila]

I am using Sumproduct

=SUMPRODUCT(--(HomeTeam=$C$2),--(AwayTeam=B5), HomeScore)

to put team scores on a team sheet, but still get a score of 0 - 0
when the game hasnt been played and no score is entered onto the
'schedule' sheet. This is not ideal. How can i have NOTHING go into
the cells when the game has not been played. These scores come from a
'schedule' sheet with all games for the year listed.

any help?

sheila
TIA

 

[Dave Peterson]

=if(count(homescore)=0,"Nothing",yourformulahere)

 

[Sheila]

Sorry Dave, no that doesnt work.

Please see previous post titled "Summarising scores"

TIA

Sheila

 

[Sheila]

I even laughed at your humour too, yes I know I said i want to put
NOTHING in the cell, but i just dont want the digit '0' in there.

sheila

 

[Peo Sjoblom]

It's better to keep to the original thread instead of referring people who
are trying to help to look up your previous posts. Maybe you could explain
how the table is set up and why the result is zero, what you probably should
do is to redesign the table so zero without any games played is not possible

 

[Biff]

Hi!

If you'd like to, you can send me your file and I'll take a look. I'm at:

xl "can" help at comcast period net

Get rid of the quoted stuff and change the obvious.

Biff

 

[Sheila]

Tried that, but nobody looked at my follow up post >(*.*)<

Sheila

 

[Dave Peterson]

Ah, you only want to check if that team in $c$2 has been used:

=IF(SUMPRODUCT(--(hometeam=$C$2),--(ISNUMBER(homescore)))=0,"",
SUMPRODUCT(--(hometeam=$C$2),--(awayteam=B5),homescore))

(all one cell)

 

[Sheila]

Nope, sorry Dave, that doesnt work either.

Never mind, I will put up with it the way it is, thanks for trying.

Sheila

 

[Biff]

Nope, sorry Dave, that doesnt work either.

Never mind, I will put up with it the way it is, thanks for trying.

Oh well!

Biff

 

[Guest]

Have you tried putting dashes in the cells with no scores yet?

 

[Biff]

If anybody is still following this thread....

Sheila sent me her file.

The problem involved having to test specific cells within two different
named ranges for "". Zero is a possible score so testing for for a sum of
zero wouldn't work.

Here's what I came up with:

Array entered:

For the Home Team score:

=IF(OFFSET(Rounds!$A$1,MATCH(1,(HomeTeam=B6)*(AwayTeam=$C$2),0),0)="","",SUMPRODUCT(--(HomeTeam=B6),--(AwayTeam=$C$2),AwayScore))

For the Away Team score:

=IF(OFFSET(Rounds!$A$1,MATCH(1,(HomeTeam=B6)*(AwayTeam=$C$2),0),4)="","",SUMPRODUCT(--(HomeTeam=B6),--(AwayTeam=$C$2),HomeScore))

The file would require a lot of these formulas and calc time will be slower.

Biff

 

[Sheila]

and it works just fine Biff, thanks very much

sheila

 

[Biff]

Oh hey, I came up with something slightly more efficient. I used an INDEX
function in place of OFFSET.

Check your inbox!

Biff

 

[Sheila]

Have done and put that in. Thanks, never thought about INDEX/MATCH
for this purpose.

Sheila