rachelmads2002
New Member
- Joined
- Apr 20, 2017
- Messages
- 3
Hi guys, I'm Rachel and new to this site. I might have a hard time explaining this so bare with me. I'm trying to find out a formula that will show how many times a specific team plays against teams from a specific conference and how many times that specific team has won and lost against teams in a specific conference.
Let me provide a basic layout.
I have a workbook that has 10 pages. The first 8 pages are titled: #1 vs #16, #2 vs #15, #3 vs #14, #4 vs #13, #5 vs #12, #6 vs #11, #7 vs #10, #8 vs #9. On those pages i have tables with Data from T4:AB135. Column U from U4:U135 is the year column. Every 4 lines are the same year such as U4:U7 = 1985, U8:U11 = 1986, etc (there are 4 games of a #1 seeded team vs #16 seeded team every year). Column V from V4:V135 = Every #1 ranked team for each year. Column X from X4:X135 = Every #16 ranked team for each year. V4 & X4 is the first match-up of a #1 seeded team vs #16 seeded team in 1985, while V135 & X135 are the last match-up of a #1 vs #16 seeded teams in 2017. Column Y from Y4:Y135 = the score of the #1 seeded team, and column Z from Z4:Z135 is the score of the #16 seeded team. Column AB from AB4:AB135 = The margin of victory between the two scores. Positive numbers mean that a #1 seeded team won the match-up while negative numbers have conditional formatting to indicate an upset where a #16 seeded team has beaten a #1 seeded team. The cell ranges remain the same over all 8 pages.
The 9th page is titled "Team IDs" and it too has a table that shows the names of each team and their respected conference. The table on this page runs from A1:A352 with headings. The actual team names begin in A2. Column A = team name and column D = conference name.
The 10th page is titled "Conference Stats" and this is the page in which I need a couple formulas. This page also has a table extending from A3:DZ354. Column A from A3:A354 = Team names of all teams eligible to play in the tournament every year. They're listed in alphabetical order. Team names actually start in A4, while A3 is a heading. Column B shows how many times since 1985 that each team has made a tournament appearance. Again the total amount begins in B4 while B3 is a heading. Going forward from there every 4 columns are conference specific. Column C is the amount of times each team in Column A has played against the conference titled "American East", Column D is the total amount of times a team in Column A has beaten teams from the American East. Column E is the total amount of times a team in Column A has lost against teams from the American East while the final column, column F is the win%.
This format repeats itself for the next conference from Column G:J and so on down the line until it gets to column DZ.
So for example: The team Alabama is listed in cell A7 and they have been in the tournament 15 times since 1985. I'd like to find out how many of those 15 appearances are against each conference. From there I need to find out how many wins and losses Alabama has had against each conference.
I'd like to keep cells empty if there aren't any appearances, wins, or losses. I'd prefer to not have this entire page filled with zeros. So for example if Alabama has never played a team from the American East than I'd like Cell C7, D7, E7, F7 to remain empty. Now lets say that Alabama has played 5 times against the American East, but each time they lost I'd like the win% column in F7 so indicate 0%.
I hope i haven't confused anyone, but thank you to anyone who takes the time to assist me! I didn't see an option to upload the workbook, but I'll provide my email in case someone would prefer to have the workbook emailed to them.
Thank you again!!!
Rachel
Let me provide a basic layout.
I have a workbook that has 10 pages. The first 8 pages are titled: #1 vs #16, #2 vs #15, #3 vs #14, #4 vs #13, #5 vs #12, #6 vs #11, #7 vs #10, #8 vs #9. On those pages i have tables with Data from T4:AB135. Column U from U4:U135 is the year column. Every 4 lines are the same year such as U4:U7 = 1985, U8:U11 = 1986, etc (there are 4 games of a #1 seeded team vs #16 seeded team every year). Column V from V4:V135 = Every #1 ranked team for each year. Column X from X4:X135 = Every #16 ranked team for each year. V4 & X4 is the first match-up of a #1 seeded team vs #16 seeded team in 1985, while V135 & X135 are the last match-up of a #1 vs #16 seeded teams in 2017. Column Y from Y4:Y135 = the score of the #1 seeded team, and column Z from Z4:Z135 is the score of the #16 seeded team. Column AB from AB4:AB135 = The margin of victory between the two scores. Positive numbers mean that a #1 seeded team won the match-up while negative numbers have conditional formatting to indicate an upset where a #16 seeded team has beaten a #1 seeded team. The cell ranges remain the same over all 8 pages.
The 9th page is titled "Team IDs" and it too has a table that shows the names of each team and their respected conference. The table on this page runs from A1:A352 with headings. The actual team names begin in A2. Column A = team name and column D = conference name.
The 10th page is titled "Conference Stats" and this is the page in which I need a couple formulas. This page also has a table extending from A3:DZ354. Column A from A3:A354 = Team names of all teams eligible to play in the tournament every year. They're listed in alphabetical order. Team names actually start in A4, while A3 is a heading. Column B shows how many times since 1985 that each team has made a tournament appearance. Again the total amount begins in B4 while B3 is a heading. Going forward from there every 4 columns are conference specific. Column C is the amount of times each team in Column A has played against the conference titled "American East", Column D is the total amount of times a team in Column A has beaten teams from the American East. Column E is the total amount of times a team in Column A has lost against teams from the American East while the final column, column F is the win%.
This format repeats itself for the next conference from Column G:J and so on down the line until it gets to column DZ.
So for example: The team Alabama is listed in cell A7 and they have been in the tournament 15 times since 1985. I'd like to find out how many of those 15 appearances are against each conference. From there I need to find out how many wins and losses Alabama has had against each conference.
I'd like to keep cells empty if there aren't any appearances, wins, or losses. I'd prefer to not have this entire page filled with zeros. So for example if Alabama has never played a team from the American East than I'd like Cell C7, D7, E7, F7 to remain empty. Now lets say that Alabama has played 5 times against the American East, but each time they lost I'd like the win% column in F7 so indicate 0%.
I hope i haven't confused anyone, but thank you to anyone who takes the time to assist me! I didn't see an option to upload the workbook, but I'll provide my email in case someone would prefer to have the workbook emailed to them.
Thank you again!!!
Rachel
Last edited by a moderator:
