Multiple formulas needed for March Madness Stats Workbook

Hi guys. I'm making a workbook for my kid brother who loves March Madness. He's obsessed, it's so cute.

This is multifaceted so i hope someone can spare a little time to help me answer these questions. Thanks in advance!

Problem #1

In the upset column (B) of page "#1 vs #16 i would like to calculate the total amount of upsets per year based on the data within the range T:AB. If a negative number appears in column AB then i'd like to reflect this in the corresponding cell in Column B.

My only requirements is that this formula would account for the 4 lines in the T:AB because there are 4 games per year, it's easily duplicated as time goes on and T:AB extends, and finally that the calculation in Column B remains blank and not 0 when no upsets have occurred in a specific year.

Problem #2
The range of D:R is just a different layout for any upsets that have occurred in a specific year as depicted in the range T:AB. Let's use the sheet #8 vs #9 as a reference. In 1985 two upsets occurred where a #9 beat a #8. They are highlighted in Red. What i want to D:R to look like is as depicted. I just don't want to manually enter the information in D:R. I want it to appear as an upset is recorded in T:AB. D:R must remain empty until an upset is recorded in T:AB. Upsets may not be recorded consecutively as outlined in the range T4:AB7. There will never be more than 4 games each year, but there can be 0, 1, 2, 3, or 4 upsets and finally the upsets that are displayed in D:R must flow consecutively meaning that there may be an upset in T4:AB4, and then in T6:AB6 for the year of 1985 and they need to be displayed in the range D4:F4 & H4:J4 respectively.

Problem #3
On the "conference success" page there's a breakdown of each team and their tournament appearances (range C:B). I would like to find out each team's win loss record when playing other teams from a specific conference without manually calculating. As games are recorded in pages #1 vs #16, #2 vs #15, etc the data needs to correspond to range C4:DY354 of the "conference success" page. On the page titled "Team Names" each team has a corresponding conference for reference.

For example: Albany who is from the "American East" conference has appeared 5 times since 1985. Two times they appeared as a #16 playing against Uconn in 2006 and Florida in 2014. They lost both games. Uconn is in "The American" conference and Florida is the "SEC" Albany has appeared as a #15 one time losing in 2013 to Duke who is from the "ACC" conference; they also appeared as a #14 seed in 2015 losing to Oklahoma who is from the "Big 12" conference. Finally in 2007 they appeared as a #13 and lost to Virginia who is also from the ACC.

I have manually recorded the losses, but i would prefer a formula to account for the data. My only requirements are that cells in C4:DY354 remain empty if certain teams have not appeared in the tournament or have not competed against specific conferences, such as the case with Albany.

Problem #4
Using the page "Seed Success" for discussion, I'm looking for a few formulas.

Formula 1:
In Column F, i am looking for the overall win % of the team Albany. I want the answer to display if there are wins present, but remain blank if only losses are present. So if Albany has been in the tournament 5 times and have never won than cell F4 should remain empty. If next year Albany is in the tournament and wins than cell F4 should display 17% (1 win divided by 6 appearances).

Formula 2:
In cell I4 I'm looking for the total amount of times Albany has played a #1 seed. Like other situations it should remain empty if they've never played against a number 1 seeded team.

Formula 3:
This would be a formula that calculates the total times Albany has beaten a #1 seeded team. It too would have to remain blank if 0. The result would appear in cell J4.

Formula 4:
The would be a formula that calculates the total times Albany has lost against a #1 seeded team. It too would have to remain blank if 0. The result would appear in cell K4.

Formula 5:
Very similar to the formula #1 in Cell F4, but not accounting for total overall win %, but only win % against number 1 seeded teams.

Problem #5
Sheet 4 is a view of a drop down table. From all the data contained in all pages of this workbook i would like create a simple drop down table.

Step 1:
Pick a team from the drop down list.

Step 2:
Pick a conference from a drop down list. Other options would be "Versus" & "Ranking".

Step 3:
Pick the category in which i want an answer, such as Wins. If i picked the team North Carolina, the conference SEC, and the answer Wins the result would show many wins North Carolina has had against SEC opponents. If i choose losses than it would display the total amount times North Carolina has lost against SEC teams. Win % would be self explanatory.

If in step 2 i choose "versus" or "Ranking" instead of "conference" than i would also chose from a drop down list 1-16 and continue on with Step 3. This would display the wins, losses, and win % of Albany vs #1 seeded teams, #2 seeded teams, etc. It would also display the win/loss/win% of Albany based on their own ranking if i choose the option "ranking".

I hope this makes sense. The file is attached below. Thank you for anyone who takes the time to help.

should i have broken this down into multiple threads? i was afraid i'd clog up the board with a lot of different threads.

Hi,

I have no idea on how to solve 3 & 4 & 5

but 1 & 2 is solved.
you can see for the attachment. "help (after).xlsx"

Can u give more example on 5, for the list option in the conference and the versus and ranking and the result need to the win and lose.

And the problem 3, I find no array formula to replace the opposite team convert to the conference. (substitute is not working as I expected...)

attached is my test "that help.xlsx". hope some one help on this also...