+ Reply to Thread
Results 1 to 25 of 25

Google Sheets: Partnership Scores

  1. #1
    Forum Contributor
    Join Date
    08-19-2012
    Location
    Sydney
    MS-Off Ver
    MS Office 365/16
    Posts
    282

    Google Sheets: Partnership Scores

    Hi,

    In the attached document in the black font, yellow background is the data i have. In the red font below is the result I am trying to generate from the above data.

    I have provided 2 examples. You will see Tabs Example A and Example B


    Many thanks as always
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    2,103

    Re: Partnership Scores

    How do you generate the partnership value in the new table? Generating the FOW is clear.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,373

    Re: Partnership Scores

    The partnership value is the number of runs scored between the fall of consecutive wickets.

    It's going to be tricky, this one, getting the names. Mmm.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  4. #4
    Forum Contributor
    Join Date
    08-19-2012
    Location
    Sydney
    MS-Off Ver
    MS Office 365/16
    Posts
    282

    Re: Partnership Scores

    it is quite difficult to explain. Here is a link of the proper document. Look at rows 43-45 column B-P; rows 120-122 columns B-P; rows 197-199 columns B-P.

    https://docs.google.com/spreadsheets...it?usp=sharing

    It is close to what I am looking for, but issues arise when under FOW numbers are the same.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,373

    Re: Partnership Scores

    Does this solution need to be for Google Sheets? And why didn’t you say that you already had a formula that is almost what you want?
    Last edited by AliGW; 01-08-2021 at 05:48 PM.

  6. #6
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Partnership Scores

    If you have two (or more) batsman out for the same score, how do you know in which order they were out?

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,373

    Re: Partnership Scores

    That’s what is needed, I think - a helper column with the order of dismissal.

  8. #8
    Forum Contributor
    Join Date
    08-19-2012
    Location
    Sydney
    MS-Off Ver
    MS Office 365/16
    Posts
    282

    Re: Partnership Scores

    The name that is on top is the first batsman out.

    So if there are three in a row in the FOW column for example:

    Geoff = 29
    Tom = 29
    Nick = 29

    the order of dismissal would be

    Geoff
    Tom
    Nick

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,373

    Re: Partnership Scores

    But in the Google Sheet (does this need to be for Google Sheets?), the FoW list is in the batting order, and that might not be the same as the dismissal order ...

  10. #10
    Forum Contributor
    Join Date
    08-19-2012
    Location
    Sydney
    MS-Off Ver
    MS Office 365/16
    Posts
    282

    Re: Partnership Scores

    Firstly yes it does have to be for google sheets.

    The dismissal order is based from the score of the FOW. For example

    FOW Score Order of Dismissal
    0 1
    2 2
    23 4
    13 3
    78 6
    56 5
    120 8
    100 7
    Last edited by sirdon; 01-09-2021 at 03:20 AM.

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,373

    Re: Partnership Scores

    Yes, we know that, but the problem is the one you stared yourself: how do we know the order when there are multiple falls of wickets for the same number of runs? That’s why I suggested you need another column that gives this information - something that uniquely identifies the order. Could be a timestamp, for example. You need to decide what it is.

  12. #12
    Forum Contributor
    Join Date
    08-19-2012
    Location
    Sydney
    MS-Off Ver
    MS Office 365/16
    Posts
    282

    Re: Google Sheets: Partnership Scores

    I the google sheet column H in red font you will see i have added in numbers to identify when they got dismissed. Hope this helps?

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,373

    Re: Google Sheets: Partnership Scores

    Your sheet is view only, so you'll have to try this yourself.

    Instead of:

    =IFERROR(SMALL($G20:$G34,1),"")

    try this:

    =IFERROR(INDEX($G20:$G34,MATCH(SMALL($H20:$H34,1),0)),"")

  14. #14
    Forum Contributor
    Join Date
    08-19-2012
    Location
    Sydney
    MS-Off Ver
    MS Office 365/16
    Posts
    282

    Re: Google Sheets: Partnership Scores

    Sorry about that. I have changed it to edit mode.

    https://docs.google.com/spreadsheets...it?usp=sharing

    I am mainly trying to fix up row 43 and get the correct names.

  15. #15
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,373

    Re: Google Sheets: Partnership Scores

    OK - I’m out, I’m afraid. Someone else will be able to help, I hope.

  16. #16
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,373

    Re: Google Sheets: Partnership Scores

    By the way, the title of this thread is completely misleading: I’ve been focusing on scores, not names, all this time.

  17. #17
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Google Sheets: Partnership Scores

    There is nothing in the sheet that says which batsman was out first when two or more are out for the same total number of runs.
    You're formula in col H is just making an assumption which could easily be wrong.

  18. #18
    Forum Contributor
    Join Date
    08-19-2012
    Location
    Sydney
    MS-Off Ver
    MS Office 365/16
    Posts
    282

    Re: Google Sheets: Partnership Scores

    Fluff13, yes you are correct, but for now if the formula in row 43 can be based from column H this should work for now. Then when i update my next version, I will make a field in the google form (where this data originates from) to input what number dismissal that specific batsman was out.

    AliGW, apologies for the confusion. I do appreciate the help you have offered so far. You help is always so grateful.

  19. #19
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Google Sheets: Partnership Scores

    I've just realised that Sheets doesn't have the aggregate, which I would have used, so not sure.

  20. #20
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Google Sheets: Partnership Scores

    Change of plan, how about in B43 copied right
    =TEXTJOIN(" & ",1,FILTER($B$20:$B$34,($H$20:$H$34=COLUMNS($B3:B43))+($H$20:$H$34=COLUMNS($B43:C43))))

  21. #21
    Forum Contributor
    Join Date
    08-19-2012
    Location
    Sydney
    MS-Off Ver
    MS Office 365/16
    Posts
    282

    Re: Google Sheets: Partnership Scores

    Fluff13, thank you so much. That seems to work a treat.

    Apologies for the slow reply, i've been away without internet access.

    Fluff13 or AliGW can i please ask for one more favour. Can you have a look at the link again and go to the tab called 'Statistics' and look at cell E39. I can't seem to understand why it is coming up with an empty output.

    Many thanks as always.

  22. #22
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Google Sheets: Partnership Scores

    Fluff13, thank you so much. That seems to work a treat.
    You're welcome & thanks for the feedback.
    As for your new question, I'm afraid I don't know enough about sheets to be able to help.

  23. #23
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,373

    Re: Google Sheets: Partnership Scores

    Me, neither - sorry.

  24. #24
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Google Sheets: Partnership Scores

    It's because you have #N/A errors in column E on the first sheet. You could just change the formula to:

    =arrayformula(MAX(FILTER(Scoresheets!E4:E,Scoresheets!$A4:$A=$A39)*1))
    Rory

  25. #25
    Forum Contributor
    Join Date
    08-19-2012
    Location
    Sydney
    MS-Off Ver
    MS Office 365/16
    Posts
    282

    Re: Google Sheets: Partnership Scores

    brilliant stuff rorya. Thank you and to everyone. Always much appreciated

+ 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. Google Sheets Master Sheet that pulls in data from other sheets with the last edit date
    By Badvgood in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 2
    Last Post: 12-19-2020, 12:04 PM
  2. dynamic formula to count through existing and new sheets(In google sheets)
    By wlinksanju in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 1
    Last Post: 11-26-2020, 09:16 PM
  3. Google Apps Script for Google Sheets Pulling Formulas from Master to Several Slave Sheets
    By excelroofing in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 4
    Last Post: 08-22-2018, 02:06 AM
  4. email row contents based on cell values (google sheets populated by google forms)
    By reedg in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 0
    Last Post: 01-13-2016, 02:55 PM
  5. Hello From Denver - Colorado Emergency preparedness Partnership
    By DanaHoff in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 03-13-2014, 02:28 PM
  6. Replies: 1
    Last Post: 01-25-2014, 02:10 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