formula question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ML - 14 Jan 2007 23:15 GMT

Hi.
I'm working on a spreadsheet of soccer fixtures and results for a school
project.
I've done formulas to result in games won, games played, points etc.
What I'm having problem with is the draw scores.
Say
Arsenal 2 v 2 Chelsea
MUnited 1 v 1 NCUnited
AVilla v Liverpool

The formula I used is
=IF(B1=D1, 1, 0) + IF(B2=D2, 1, 0) + IF(B3=D3, 1, 0)
but if you haven't entered a score yet like for the 3rd row and the cells
are empty it counts it as 1 as they are still equal.
please help
Thanks

Reply to this Message

Sandy Mann - 14 Jan 2007 23:26 GMT

Try:

=IF(and(B1=D1,B1<>""), 1, 0) + IF(and(B2=D2,B2<>""), 1, 0) +
IF(and(B3=D3,B3<>""), 1, 0)

Signature

HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace@mailinator.com with @tiscali.co.uk

> Hi.
> I'm working on a spreadsheet of soccer fixtures and results for a school
[quoted text clipped - 12 lines]
> please help
> Thanks

Reply to this Message

daddylonglegs - 15 Jan 2007 00:08 GMT

especially if you have more than 3 results you might want to use

=SUMPRODUCT(--(B1:B3=D1:D3),--(B1:B3<>""))

> Try:
>
[quoted text clipped - 17 lines]
> > please help
> > Thanks

Reply to this Message

ML - 18 Jan 2007 17:04 GMT

Thanks everyone.
The =IF(AND(..... worked a treat
M

> Try:
>
[quoted text clipped - 17 lines]
>> please help
>> Thanks

Reply to this Message

T. Valko - 15 Jan 2007 03:42 GMT

Try this:

=SUMPRODUCT(--(B1:B3<>""),--(D1:D3<>""),--(B1:B3=D1:D3))

Biff

> Hi.
> I'm working on a spreadsheet of soccer fixtures and results for a school
[quoted text clipped - 12 lines]
> please help
> Thanks

Reply to this Message