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

=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

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
<[email protected]> 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

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 <[email protected]>
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
><[email protected]> 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

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" <[email protected]> wrote in message
news:[email protected]...
>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 <[email protected]>
> 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
>><[email protected]> 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
>

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" <[email protected]> wrote in message
news:[email protected]...
>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 <[email protected]>
> 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
>><[email protected]> 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
>

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

Sheila


On Fri, 13 May 2005 19:49:02 -0700, "Peo Sjoblom" <[email protected]>
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

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
> <[email protected]> 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

--

Dave Peterson

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
<[email protected]> 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
>> <[email protected]> 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

>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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>>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 <[email protected]>
>> 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
>>><[email protected]> 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
>>
>
>

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
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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
> <[email protected]> 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
>>> <[email protected]> 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
>

and it works just fine Biff, thanks very much

sheila


On Mon, 16 May 2005 14:47:57 -0400, "Biff" <[email protected]>
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 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" <[email protected]> wrote in message
>news:[email protected]...
>> 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
>> <[email protected]> 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
>>>> <[email protected]> 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
>>
>

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

Check your inbox!

Biff

"Sheila" <[email protected](remove underscores)> wrote in message
news:[email protected]...
> and it works just fine Biff, thanks very much
>
> sheila
>
>
> On Mon, 16 May 2005 14:47:57 -0400, "Biff" <[email protected]>
> 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 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" <[email protected]> wrote in message
>>news:[email protected]...
>>> 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
>>> <[email protected]> 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
>>>>> <[email protected]> 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
>>>
>>
>

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" <[email protected]>
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" <[email protected](remove underscores)> wrote in message
>news:[email protected]...
>> and it works just fine Biff, thanks very much
>>
>> sheila
>>
>>
>> On Mon, 16 May 2005 14:47:57 -0400, "Biff" <[email protected]>
>> 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 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" <[email protected]> wrote in message
>>>news:[email protected]...
>>>> 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
>>>> <[email protected]> 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
>>>>>> <[email protected]> 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
>>>>
>>>
>>
>