ExcelBanter - Game score without game being played

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Game score without game being played (https://www.excelbanter.com/excel-worksheet-functions/26141-game-score-without-game-being-played.html)

Sheila

Game score without game being played

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 wrote:

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

Sheila

Sorry Dave, no that doesnt work.

Please see previous post titled "Summarising scores"

TIA

Sheila

On Fri, 13 May 2005 18:05:28 -0500, Dave Peterson
wrote:

=if(count(homescore)=0,"Nothing",yourformulaher e)

Sheila wrote:

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

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

On Sat, 14 May 2005 14:35:43 +1200, Sheila
wrote:

Sorry Dave, no that doesnt work.

Please see previous post titled "Summarising scores"

TIA

Sheila

On Fri, 13 May 2005 18:05:28 -0500, Dave Peterson
wrote:

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

Sheila wrote:

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

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

--
Regards,

Peo Sjoblom

"Sheila" wrote in message
...
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

On Sat, 14 May 2005 14:35:43 +1200, Sheila
wrote:

Sorry Dave, no that doesnt work.

Please see previous post titled "Summarising scores"

TIA

Sheila

On Fri, 13 May 2005 18:05:28 -0500, Dave Peterson
wrote:

=if(count(homescore)=0,"Nothing",yourformulaher e)

Sheila wrote:

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

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

On Sat, 14 May 2005 14:35:43 +1200, Sheila
wrote:

Sorry Dave, no that doesnt work.

Please see previous post titled "Summarising scores"

TIA

Sheila

On Fri, 13 May 2005 18:05:28 -0500, Dave Peterson
wrote:

=if(count(homescore)=0,"Nothing",yourformulaher e)

Sheila wrote:

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

Sheila

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

Sheila

On Fri, 13 May 2005 19:49:02 -0700, "Peo Sjoblom"
wrote:

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

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 wrote:

Sorry Dave, no that doesnt work.

Please see previous post titled "Summarising scores"

TIA

Sheila

On Fri, 13 May 2005 18:05:28 -0500, Dave Peterson
wrote:

=if(count(homescore)=0,"Nothing",yourformulaher e)

Sheila wrote:

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

Sheila

Nope, sorry Dave, that doesnt work either.

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

Sheila

On Sat, 14 May 2005 07:23:33 -0500, Dave Peterson
wrote:

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 wrote:

Sorry Dave, no that doesnt work.

Please see previous post titled "Summarising scores"

TIA

Sheila

On Fri, 13 May 2005 18:05:28 -0500, Dave Peterson
wrote:

=if(count(homescore)=0,"Nothing",yourformulaher e)

Sheila wrote:

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

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

"Biff" wrote in message
...
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" wrote in message
...
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

On Sat, 14 May 2005 14:35:43 +1200, Sheila
wrote:

Sorry Dave, no that doesnt work.

Please see previous post titled "Summarising scores"

TIA

Sheila

On Fri, 13 May 2005 18:05:28 -0500, Dave Peterson
wrote:

=if(count(homescore)=0,"Nothing",yourformulahe re)

Sheila wrote:

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

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

"Sheila" wrote:

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

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 sco

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

For the Away Team sco

=IF(OFFSET(Rounds!$A$1,MATCH(1,(HomeTeam=B6)*(Away Team=$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" wrote in message
...
Nope, sorry Dave, that doesnt work either.

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

Sheila

On Sat, 14 May 2005 07:23:33 -0500, Dave Peterson
wrote:

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 wrote:

Sorry Dave, no that doesnt work.

Please see previous post titled "Summarising scores"

TIA

Sheila

On Fri, 13 May 2005 18:05:28 -0500, Dave Peterson
wrote:

=if(count(homescore)=0,"Nothing",yourformulaher e)

Sheila wrote:

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

Sheila

and it works just fine Biff, thanks very much

sheila

On Mon, 16 May 2005 14:47:57 -0400, "Biff"
wrote:

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 sco

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

For the Away Team sco

=IF(OFFSET(Rounds!$A$1,MATCH(1,(HomeTeam=B6)*(Awa yTeam=$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" wrote in message
.. .
Nope, sorry Dave, that doesnt work either.

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

Sheila

On Sat, 14 May 2005 07:23:33 -0500, Dave Peterson
wrote:

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 wrote:

Sorry Dave, no that doesnt work.

Please see previous post titled "Summarising scores"

TIA

Sheila

On Fri, 13 May 2005 18:05:28 -0500, Dave Peterson
wrote:

=if(count(homescore)=0,"Nothing",yourformulaher e)

Sheila wrote:

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

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" (remove underscores) wrote in message
...
and it works just fine Biff, thanks very much

sheila

On Mon, 16 May 2005 14:47:57 -0400, "Biff"
wrote:

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 sco

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

For the Away Team sco

=IF(OFFSET(Rounds!$A$1,MATCH(1,(HomeTeam=B6)*(Aw ayTeam=$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" wrote in message
. ..
Nope, sorry Dave, that doesnt work either.

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

Sheila

On Sat, 14 May 2005 07:23:33 -0500, Dave Peterson
wrote:

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 wrote:

Sorry Dave, no that doesnt work.

Please see previous post titled "Summarising scores"

TIA

Sheila

On Fri, 13 May 2005 18:05:28 -0500, Dave Peterson
wrote:

=if(count(homescore)=0,"Nothing",yourformulaher e)

Sheila wrote:

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

Sheila

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

Sheila

On Tue, 17 May 2005 00:22:16 -0400, "Biff"
wrote:

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

Check your inbox!

Biff

"Sheila" (remove underscores) wrote in message
.. .
and it works just fine Biff, thanks very much

sheila

On Mon, 16 May 2005 14:47:57 -0400, "Biff"
wrote:

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 sco

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

For the Away Team sco

=IF(OFFSET(Rounds!$A$1,MATCH(1,(HomeTeam=B6)*(A wayTeam=$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" wrote in message
...
Nope, sorry Dave, that doesnt work either.

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

Sheila

On Sat, 14 May 2005 07:23:33 -0500, Dave Peterson
wrote:

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 wrote:

Sorry Dave, no that doesnt work.

Please see previous post titled "Summarising scores"

TIA

Sheila

On Fri, 13 May 2005 18:05:28 -0500, Dave Peterson
wrote:

=if(count(homescore)=0,"Nothing",yourformulaher e)

Sheila wrote:

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

All times are GMT +1. The time now is 10:43 PM.