MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Want to create baseball standings

Posted by Randy Evans on January 30, 2001 4:09 PM

From a game schedule table I want to create a traditional teams standings with columns of wins, loses, ties, runs allowed. I can get the runs allowed using SUMIF. The wins, etc. I can't.
Game schedule table:
1/30/01 Yankees 5 vs Giants 4
1/31/01 Giants 6 vs Yankees 7
Standings table:
Yankees 2 0 0 10
Giants 0 2 0 12

Is this even possible?

Posted by Randy Evans on February 07, 2001 8:49 PM

I got it, thanks to some prompting from MR Excel and some good old American brainstorming. What a great web site!

Posted by Ian on February 09, 2001 2:20 PM

I was interested in seeing how to accomplish this. Can you give a cliff note version on how to do it?

Posted by Randy Evans on February 10, 2001 4:25 PM

Make sure the schedule is set up in columns as shown in my original message. Using an array formula (see the tip archive on CSE formulas). You would have to add up records where column B = Yankees AND column C > column F plus any records
where column E = Yankees and column F is > column C.
One particular tip of the week which deals with CSE formulas when you have 2 conditions is the key.
Brainstorming on my own to figure it out was a challenge but it helped me to understand CSE formulas better. It was a good learning tool. The ties was the part that took awhile to get.
Let me know if you just want to see how it was done and don't need to learn how to do it yourself.
Now I'm working on a user interface for data entry so that someone doesn't mess up the formula.

Posted by Ian on February 12, 2001 2:33 PM

Thanks for the offer. I'd rather play around with it a little for my own benefit.
Thanks again,