+ Reply to Thread
Results 1 to 4 of 4

Scoring between tables looking at data set

  1. 08-16-2018, 05:25 AM #1
    matthewmunro
    matthewmunro is offline
    Registered User
    Join Date
    06-03-2018
    Location
    Auckland, New Zealand
    MS-Off Ver
    Microsoft Office Professional Plus 2013
    Posts
    16

    Scoring between tables looking at data set

    Hi,

    See attached my excel sheet with a layout and ill try explain what sort of formulas I want.

    I currently have a formula that shows an individuals win rate based on EVERY game he or she plays.

    What im looking for is a formula that shows the win percentage on certain match ups.

    In my sheet you can see that Matthew M's win percentage is 40% versing both Karl C and Kurt H, I want a formula to show the win percentage for Matthew M only versing Karl C and not including the matches with Kurt H. Similarly I want a formula that would show Karl C's win percentage solely against Matthew M.

    Just for reference, Matthew M's win percentage against Karl should end up as 29% and Karl C's win percentage against Matthew M should be 71%. (I have free typed what the percentages should be once the formula has been set up)




    On the next table, im looking for an average score in specific match-ups. I have free typed the values that im expecting for your reference. So what im trying to get is that when Matthew M verses Karl C, Matthew M gets an average score of 9.14 and Karl C gets an average score of 10.42 (this showing that although he wins more, on average he is only scoring 1 point more than me)



    I will be adding more data daily, any help would be much appreciated! Hopefully you understand what im looking for but if you have any questions then ill be happy to explain what I want the best I can.

    Kind Regards,

    Matthew
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 08-16-2018, 05:31 AM #2
    matthewmunro
    matthewmunro is offline
    Registered User
    Join Date
    06-03-2018
    Location
    Auckland, New Zealand
    MS-Off Ver
    Microsoft Office Professional Plus 2013
    Posts
    16

    Re: Scoring between tables looking at data set

    Just realized that the individual win rate formula isnt pulled down to the last line (Matthew M win percentage should be 40%, Karl C 78% and Kurt H 20%) but the rest of the post is still valid

    Matthew
    Reply With QuoteRegister To Reply
  3. 08-23-2018, 12:31 AM #3
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,557

    Re: Scoring between tables looking at data set

    I made an adjustment to the way the names are displayed in the Player vs Player columns (i.e. Matthew M vs Karl C). The first name listed represents the player for whom the percentage or number of points are assigned.
    The formula that populates the WIN% column is:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    The formula that populates the AVERAGE SCORE column is:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
    Reply With QuoteRegister To Reply
  4. 08-23-2018, 01:06 AM #4
    samba_ravi
    samba_ravi is offline
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,914

    Re: Scoring between tables looking at data set

    I Changed your table little bit and used below formulas
    please find the attached file

    K4=IFERROR(COUNTIFS($C$4:$D$15,$J4,$G$4:$H$15,"Win")/COUNTIF($C$4:$D$15,$J4),"")

    O4=IFERROR(SUMPRODUCT(($G$4:$H$15="Win")*($C$4:$C$15=CHOOSE({1,2},$M4,$N4))*($D$4:$D$15=CHOOSE({1,2},$N4,$M4)))/SUMPRODUCT(COUNTIFS($C$4:$C$15,CHOOSE({1,2},$M4,$N4),$D$4:$D$15,CHOOSE({1,2},$N4,$M4))),"")
    S4=IFERROR(SUMPRODUCT(($E$4:$F$15)*($C$4:$C$15=CHOOSE({1,2},$Q4,$R4))*($D$4:$D$15=CHOOSE({1,2},$R4,$Q4)))/SUMPRODUCT(COUNTIFS($C$4:$C$15,CHOOSE({1,2},$Q4,$R4),$D$4:$D$15,CHOOSE({1,2},$R4,$Q4))),"")
    Attached Files Attached Files
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Refresh Data Tables from Pivot Tables
    By JJones5 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 09-15-2016, 03:12 PM
  2. Filtering data from tables using pivot tables
    By DaddioPlus in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 03-05-2015, 01:25 PM
  3. Scoring
    By LMaiuri in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-08-2013, 12:27 PM
  4. Copying pivot tables as data tables with formatting
    By wolis in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-17-2012, 12:57 AM
  5. Heartbeat Data Scoring
    By SombreNote in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-09-2012, 01:32 PM
  6. Multiple Data Tables Link and Calculate onto Target Tables
    By billexchry in forum Excel General
    Replies: 6
    Last Post: 03-04-2011, 02:42 PM
  7. Creating data tables from a database without using pivot tables
    By gareth.campbell in forum Excel General
    Replies: 4
    Last Post: 11-24-2010, 12:23 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1