League standings based off final scores

[BethP]

Hi all,

I'm stuck on a project and am hoping someone can give me a little insight.
I may be taking a long and onerous way around what I'm trying to accomplish
in the first place, so feel free to re-direct me.

I have a few existing spreadsheets that I'm trying to make easier to manage.
Our softball league records scores in to the schedule page, and I would like
to standings page to update automatically from there. This is what it looks
like so far:

Scores pg (this table starts in cell B2):
Home Score Visitors Score
A 13 vs. B 5
B 7 vs. A 15
C 3 vs. D 10
D 17 vs. C 17
D 23 vs. B 23
B 4 vs. D 14
A 9 vs. C 12
C 16 vs. A 15

On that page, I hid a few columns to lookup who actually won each game. I
couldn't come up with an easy way to lookup who won just off of the scores,
so I figured this would be the easiest way to write my count formulas. It
looks like this, with these formulas in each cell (starting in H2, next to
the scores):

Winner Loser
A B
A B
D C
tie tie
tie tie
D B
C A
C A

Formulas: Winner IF(ISBLANK(C3),"",IF(C3-F3>0,B3,(IF(C3-F3<0,E3,"tie"))))
Loser IF(ISBLANK(C3),"",IF(C3-F3<0,B3,(IF(C3-F3>0,E3,"tie"))))

So on my standings page, I have this table. I calculated the wins and
losses based off those hidden columns, but with the ties, I can't figure out
how to grant each team that played 1 point there. My table and the formulas
so far (on my test page, this table starts in B17):

TEAM WON LOST TIE PCT PTS RS RA
A 2 0 1.000 4 28 12
B 0 2 0.000 0 12 28
C 2 1 0.667 4 20 27
D 1 0 1.000 2 27 20

Formulas Won tally: =COUNTIF(H3:H6,B18)
Lost tally: =COUNTIF(I3:I6,B18)
Tie tally: =??
Pct:
=IF(ISERR(SUM(C18+E18*0.5)/SUM(C18:E18)),"0",(SUM(C18+E18*0.5)/SUM(C18:E18)))
Points: =SUM(C18*2+E18)
Runs scored: =SUMIFS($C$3:$C$6,$B$3:$B$6,B18)+SUMIFS($F$3:$F$6,$E$3:$E$6,B18)
Runs against:
=SUMIFS($C$3:$C$6,$E$3:$E$6,B18)+SUMIFS($F$3:$F$6,$B$3:$B$6,B18)

I have all of the error nullification in there to prevent cluttering up the
page with games that have not played yet.

So does it look like I'm missing something easy, making more work for myself
somewhere, or completely in over my head?

Thanks a bunch!
-beth

 

[T. Valko]

I do something similar during the NFL season.

Formulas: Winner IF(ISBLANK(C3),"",IF(C3-F3>0,B3,(IF(C3-F3<0,E3,"tie"))))
Loser IF(ISBLANK(C3),"",IF(C3-F3<0,B3,(IF(C3-F3>0,E3,"tie"))))

These may be a bit better:

Winner
=IF(COUNT(C3,F3)<2,"",IF(C3>F3,B3,IF(F3>C3,E3,"Tie")))

Loser
=IF(H3="","",IF(C3=F3,"Tie",IF(H3=B3,E3,B3)))

with the ties, I can't figure out how to grant
each team that played 1 point there
=SUMPRODUCT(((B$3:B$10=B18)*(H$3:H$10="tie"))+((E$3:E$10=B18)*(I$3:I$10="tie")))

Pct:
=IF(ISERR(SUM(C18+E18*0.5)/SUM(C18:E18)),"0",(SUM(C18+E18*0.5)/SUM(C18:E18)))

Not sure how your data is setup but this is the formula I use for getting
the winning %.

Where:

B2 = wins
C2 = loses
D2 = ties

=IF(SUM(B22),ROUND((B2+D2/2)/SUM(B22),3),0)

I round the result to 3 decimal places then I use a custom number format of
..000 so that the leading 0 isn't displayed. For example: .667 vs 0.667.

I see you're using Excel 2007:

Runs scored: =SUMIFS

So, you can eliminate the If(ISERROR junk with:

=IF(ISERR(SUM(C18+E18*0.5)/SUM(C18:E18)),"0",(SUM(C18+E18*0.5)/SUM(C18:E18)))

=IFERROR(SUM(C18+E18*0.5)/SUM(C18:E18),0)

 

[BethP]

Thanks so much for the reply Biff.

For the winner/loser formulas, I don't think the "IF(COUNT(C3,F3)<2,"",..."
portion will work, because it's always feasible that someone could have a 1-0
game. (It's senior softball with a 240 foot fence, so not likely, but...)

The "tie" formula worked perfect. I still haven't been able to wrap my head
around the myriad uses of the SUMPRODUCT formula, but it sure seems to work
wonders.

And you're right, I am using 2007. However, I forgot that the SUMIFS was
new to 2007. I'll have to make sure I can keep that in here, as I don't know
if all of our scorekeepers are running 2k7 or 2k3. (I just finally upgraded
about a month ago) If so, I'll definitely play with the IFERROR funtion, as
that will be much cleaner than the extended IF(ISERR(... mess is.

(I use IF(ISERR(... with VLOOKUP *all* the time, and it make for some
awfully large clunky formulas.)

Thanks again!
beth

 

[T. Valko]

I don't think the."IF(COUNT(C3,F3)<2,"",..."

portion will work, because it's always feasible
that someone could have a 1-0 game.

That portion is only testing that scores have been entered for both teams.
The formula won't calculate the winner or loser until 2 scores have been
entered.

 

[BethP]

I did manage to catch that shortly after I posted my reply. It helps if I'm
looking at the context correctly (that you were using 'count' for non-blank
cells rather than tallying the numbers in them).

I ran in to a problem with the SUMPRODUCT on my ties where it didn't
calculate it the same on two affected teams. I'm trying to trudge through it
and figure out if I introduced an error when I transferred it to my live
multi-page workbook, but if I end up stuck, I might implore your assistance
again.

Has anyone ever mentioned that you MVPs never get the credit due? I'd have
been sunk a long time ago without some real-world intervention with Office.


beth

 

[T. Valko]

Ok, just post back if you get stuck.

Thanks!