+ Reply to Thread
Results 1 to 6 of 6

Multiple formulas needed for March Madness Stats Workbook

  1. #1
    Forum Contributor
    Join Date
    02-27-2015
    Location
    Los Angeles, California
    MS-Off Ver
    office 2010 pro
    Posts
    238

    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.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    01-19-2012
    Location
    Barrington, IL
    MS-Off Ver
    Excel 2007/2010
    Posts
    1,213

    Re: Multiple formulas needed for March Madness Stats Workbook

    --scratch that, misread requirement--
    You should hit F5, because chances are I've edited this post at least 5 times.
    Example of Array Formulas
    Quote Originally Posted by Jacc View Post
    Sorry, your description makes no sense. I just made some formula that looks interesting cause I had nothing else to do.
    Click the * below on any post that helped you.

  3. #3
    Forum Contributor
    Join Date
    02-27-2015
    Location
    Los Angeles, California
    MS-Off Ver
    office 2010 pro
    Posts
    238

    Re: Multiple formulas needed for March Madness Stats Workbook

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

  4. #4
    Forum Contributor
    Join Date
    03-22-2017
    Location
    Malaysia
    MS-Off Ver
    Excel 2010
    Posts
    230

    Re: Multiple formulas needed for March Madness Stats Workbook

    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...
    Attached Files Attached Files

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    45,442

    Re: Multiple formulas needed for March Madness Stats Workbook

    Quote Originally Posted by RachelMads02 View Post
    should i have broken this down into multiple threads? i was afraid i'd clog up the board with a lot of different threads.
    Well, you've marked the thread as solved, so most people will just ignore it! That said, what you have presented in your opening post is really off-putting: it feels like an entire project rather than a bit of help to get something working, so yes, I think you may well have been better off taking this one step at a time, or taking the whole thing to the Commercial Services section and offering some financial recompense.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    45,442

    Re: Multiple formulas needed for March Madness Stats Workbook

    It seems that you have now started breaking this down into chunks - shall I close this thread?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. March Madness API
    By maxhecht2 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-09-2017, 07:30 PM
  2. [SOLVED] Multiple formulas needed please help
    By WimpieOosthuizen in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-29-2014, 01:14 AM
  3. Formula needed for Stats
    By lamjoey in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-07-2011, 01:16 PM
  4. Help Needed Agent Stats Tracker
    By m_789 in forum Excel General
    Replies: 1
    Last Post: 01-10-2011, 11:22 AM
  5. multiple formulas needed -
    By mheinmiller in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-16-2008, 12:17 PM
  6. March Madness Spreadsheet
    By vamosj in forum Excel General
    Replies: 0
    Last Post: 03-09-2007, 01:09 PM
  7. [SOLVED] March 2005 Actual Info vs March 2006 Plan Info
    By Al in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-18-2005, 03:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1