Accounting for MT cells

  • Thread starter Thread starter Alan
  • Start date Start date
A

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
 
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!
 
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,

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)
 
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
 
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
 
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
 
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)
 
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
 
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)
 
Bob,

Yes, I've received it!

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

Thanks again!

cheers
Max
 
Back
Top