Excel formuler help please

Andybtsn

Andybtsn

Andybtsn

Don
Joined
23 Oct 2005
Posts
44,364
Location
North Yorkshire
This is probably going to be quite easy for those in the know but unfortunately I have forgotten just about everything I ever learnt in Excel.

Me and my friend play Pro Evo on the Xbox 360 and record the scores so we can have a league going with wins, losses, points and goal difference. We play three games and record them.

This is how it is laid out..

pes454545.jpg


Basically I'm manually changing the league to input the date, what formulers do I need to use to get the league to look at the scores and fill out all the respective columns.

Any help appreciated.

Thanks
 
Ok first add a bit more info:

You need a column that records the result:

I'd suggest using column F.

In Cell F2 type: =IF(C2>D2, "P1 Win", IF(D2>C2, "P2 Win", "Draw"))

Apply this down the whole of column F

In your league table add a column for "Draws". I'd recomend doing it in column J after Wins/Loses.

Counting Each players Wins

In H2 type: =COUNTIF(E:E, "P1 Win")
In H3 type: =COUNTIF(E:E, "P2 Win")


Counting Each players Loses

In I2 type: =COUNTIF(E:E, "P2 Win")
In I3 type: =COUNTIF(E:E, "P1 Win")


Counting Each players Draws

Here this assumes you insert a new column between Loses and Goals Scored. So that Loses is in column I, Draws is now column J and Goals Scored is column K

In J2 type: =COUNTIF(E:E, "Draw")
In J3 type: =COUNTIF(E:E, "Draw")


Counting Each players Goals Scored

In K2 type: =SUM(C:C)
In K3 type: =SUM(D:D)


Counting Each players Goals Conceeded

In L2 type: =SUM(D:D)
In L3 type: =SUM(C:C)


Counting Each players Goal Difference

In M2 type: =K2-L2
In M3 type: =K3-L3
 
Last edited:
div0, that is fantastic, very much appreciated :D

One thing though, I have copied the formula down on F2, but no scores means a draw so it shows about 3000 draws at the moment!

Is it possible to create another IF saying if there is no score there then do nothing rather than account it as being a draw?

Thanks a lot,

Andy
 
Yeah sorry, this should do it:

=IF(C2<>"", IF(C2>D2, "P1 Win", IF(D2>C2, "P2 Win", "Draw")), "")

Just modify the formula in F2 to that and then fill that down!

Hopefully that'll do the trick! :D
 
You must log in or register to reply here.
Back
Top Bottom