Accounting for MT cells

[Alan]

Hello All -

I'm keeping track of the records for a football league. I
calculate the teams won/loss record and display the result
in this format: W - L or 8 - 2. Here is the array formula:

=SUM(--LEFT(C5:G5,FIND("-",C5:G5)-2))&" "&"-"&" "&SUM(--MID
(C5:G5,FIND("-",C5:G5)+2,255))

This works just fine unless there are MT cells anywhere in
the range. If there are MT cells, it will return a #VALUE
error for obvious reasons. How do I account for MT
cells??? I'm thinking something along the lines of =SUM(IF
(ISTEXT........ or =SUM(IF(NOT(ISBLANK........

I'm stuck on this one! Help!

THANX
ALAN

 

[Alan]

Figured it out! Guess I just had to walk away from it for
a while. Here's what I came up with:

=SUM(IF(C5:G5<>"",--LEFT(C5:G5,FIND("-",C5:G5)-2)))&" "&"-
"&" "&SUM(IF(C5:G5<>"",--MID(C5:G5,FIND("-",C5:G5)+2,255)))

Funny how simple the solution becomes once you clear your
head!

 

[Bob Phillips]

Alan,

As you figured, this is one way
=SUM(IF(C5:G5="",0,--LEFT(C5:G5,FIND("-",C5:G5)-2))) & " " & "-" & " " &
SUM(IF(C5:G5="",0,--MID(C5:G5,FIND("-",C5:G5)+2,255)))

However, you have a nasty formula here and you have to enter the scores as
text with spaces and a hyphen. As an alternative suggestion, why not use 3
columns, first has home score, second has the hyphen , third has away score.
You can figure out how to sum them I am sure.

I do this with VBA. If you want I can send you my Premiership workbook that
does it all.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Max]

Bob,

I'll like to request for a copy of your Premiership workbook.

Thanks
Max

 

[Bob Phillips]

Max,

It's on its way.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Alan]

Hello Bob -

You think that's nasty??? <LOL> Thanks for the suggestion
and yes, I'll take you up on your offer! Here's my addy.
I'm sure you know what to do.

valko01 is at comcast dot net

THANX
Alan

 

[Bob Phillips]

Hi Alan,

It's (hopefully) on its way. I am having a few mail problems so please
confirm receipt, either here or directly.

Regards

Bob

 

[Alan]

OK Bob, I got it. Let me play around with it for a while!

THANX!

 

[Max]

Thanks Bob.

But I haven't received it yet though.

Did you send it to my "2nd" address :
xdemechanik <at>yahoo<dot>com ?

rgds
Max

 

[Bob Phillips]

Max,

I sent it to demechanik <at> yahoo <dot> com.

Should I send it to the 2nd?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Max]

Yes, appreciate it if you would.

My demechanik <at> yahoo <dot> com account
though valid, is virtually spammed to death 99.9%
of the time since around Sep '03 when the virus hit the ngs.

That's why I've had to set-up the 2nd email account..

rgds
Max

 

[Bob Phillips]

Fingers crossed, 2nd time lucky.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Max]

Bob,

Yes, I've received it!

Impressively marvellous.
Looks every bit a fine piece of work.

Thanks again!

cheers
Max