+ 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
    Excel 2010
    Posts
    214

    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
    Valued Forum Contributor
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    1,335

    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
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    45,376

    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!

    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.

  4. #4
    Forum Contributor
    Join Date
    08-19-2012
    Location
    Sydney
    MS-Off Ver
    Excel 2010
    Posts
    214

    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
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    45,376

    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
    9,189

    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
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    45,376

    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
    Excel 2010
    Posts
    214

    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
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    45,376

    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
    Excel 2010
    Posts
    214

    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
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    45,376

    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
    Excel 2010
    Posts
    214

    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
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    45,376

    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
    Excel 2010
    Posts
    214

    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
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    45,376

    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
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    45,376

    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
    9,189

    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
    Excel 2010
    Posts
    214

    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
    9,189

    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
    9,189

    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
    Excel 2010
    Posts
    214

    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
    9,189

    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
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    45,376

    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
    2016 primarily
    Posts
    5,462

    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
    I drink, and I know things

  25. #25
    Forum Contributor
    Join Date
    08-19-2012
    Location
    Sydney
    MS-Off Ver
    Excel 2010
    Posts
    214

    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