How to calculate points for hockey team based on scores in regular time v OT

[mrrsquared79]

What I am trying to do is have the spreadsheet calculate then total the following: win(+2 points), loss(0 points), tie(1 point), overtime win(+2 point), overtime loss(1 point).

Scenario 1:

My team 2 vs opponent 1

Need spreadsheet to a) show a Win b) calculate 2 points

Scenario 2:

My team 1 v opponent 2

Need spreadsheet to a) show a Loss b) calculate 0 points

Scenario 3:

My team 2 v opponent 2

Need spreadsheet to a) show a Tie b) calculate 1 point

Scenario 4:

My team 2 v opponent 1* Overtime Win

Need spreadsheet to a) show an Overtime win b) calculate 2 points

Scenario 5:

My team 2 v opponent 3* Overtime Loss

Need spreadsheet to a) show an Overtime loss b) calculate 1 point


Now that being said do I need to somehow format the overtime wins/losses so that when the formulas are run will recognize the different formatting to tally the overtime wins or losses?

I have attached the spreadsheet so that some can dissect it and tell me how to accomplish this.

TIA

Randy

[Spencer101]

Hi Randy,

Would putting in a new column to show OT win/loss be a problem?
Because as far as I can see, if you could do that, then all you need above can be accomplished with nested IFs and a few ANDs.

[mrrsquared79]

I suppose that is feasible, look at http://www.socalstreethockey.com/Standings.html in the copper division under the spring '12 regular season for additional insight.

[Spencer101]

And which of the worksheets in your example workbook are we looking at to make these changes on?

[mrrsquared79]

And which of the worksheets in your example workbook are we looking at to make these changes on?

Bad Company not Bad Company(2). Bad Company(2) is a copy I created as a scratch to try and figure this out.

[Spencer101]

How's this?

Cells/Columns I've added and formulas I've altered are highlighted.

I've also added a "Lookup" worksheet to make allocating points to each game easier. This can also be hidden.

Let me know if this does the trick for you.

S.

[mrrsquared79]

How's this?

Cells/Columns I've added and formulas I've altered are highlighted.

I've also added a "Lookup" worksheet to make allocating points to each game easier. This can also be hidden.

Let me know if this does the trick for you.

S.

Almost there, is it possible to add a column down at the bottom after Total Wins/Total Losses/Ties/OTL thus eliminating the Game Points column that was added altogether?

[Spencer101]

So you just want to add OTL and not OTW?

[mrrsquared79]

I suppose that is feasible, look at http://www.socalstreethockey.com/Standings.html in the copper division under the spring '12 regular season for additional insight.

Please see website following the instructions provided in my to get an idea of the layout of what I am looking for when it comes to Wins Losses Ties OTL.

[mrrsquared79]

So you just want to add OTL and not OTW?

Yes, perhaps I should have been a little more clear. I apologize!

A Win and OTW both generate 2 points, the harder one is if it is a OTL as 1 point is awarded.

Again my apologies!

[Spencer101]

No worries. I realised that after I replied.

How's this?

[Spencer101]

And this time with the attachment. OOPS!