Formula help please

Question

I'm hoping to help a friend setting up an automated excel spreadsheet that will work out averages etc for their skittles team

I hate to say my excel knowledge is very basic, so wondered if anyone here could help please?

I currently have two sheets

Sheet 1 will have the scores of the games and each persons score - that I can set up (as below)

Sheet 2 is where I come unstuck, is it possible to enter a formula for the following:

Column B to add up how many games have been played from info on sheet 1 by each player?

I think spares and 9's will have to be entered manually as will make sheet 1 very full of info

Is there a formula that can detect the highest score from Sheet 1 and insert in J,K 1?

Is there a formula that can detect the most spares from Sheet 2 and insert in J,K 3?

Is there a formula that can detect the highest score from Sheet 2 and insert in J,K 5?

I assume the above three may be a problem is more than one person has the highest?

If anyone has any previous experience of something similar that can suggest anything I would be grateful for any help

Answer 1

Hi Mark Newman4,
Thanks for your post in Microsoft Community.

I'll respond to the four questions you marked in bold:

1. I'm not quite sure about the number of games each player has played in sheet1. Are you referring to the number of entries for each player in column A? For example, for player Annette, from your screenshot, I'm not sure how the data in cell B2 (27) was obtained in sheet2. Could you please share more context with me?
2. My understanding is to check cells H3 to M7 in sheet1 and find the highest score and the highest scorer.
   • For cell J1 in sheet2, use the MAX function to directly search for the maximum value.
     =MAX(Sheet1!H3:Sheet1!M7)
     
   • For cell K1 in sheet2, to accommodate the possibility of multiple highest scorers, I used the TEXTJOIN function to concatenate the results.
     =TEXTJOIN(", ", TRUE, IF(Sheet1!H3:Sheet1!M7 = J1, Sheet1!H2:Sheet1!M2, "Not Found"))
3. My understanding is to check cells F2 to F8 in sheet2 and look up who has the most spares.
   • For cell J3 in sheet2, use the MAX function to directly search for the maximum value.
     =MAX(F2:F8)
   • For cell K3 in sheet2. However, based on the screenshot, player Annette doesn't seem to have as many as five pieces, which doesn't match the result in your screenshot.
     =TEXTJOIN(", ", TRUE, IF(F2:C4 = J3, A2:A8, "Not Found"))
     
4. From the screenshot, cells J5 and K5 in Table 2 seem to describe finding the player who holds the most "9"s. However, your description seems to be searching for the highest score and the highest scorer in cells C2 to C8 in Table 2. Please let me know what your actual requirements are.

I hope the information above is helpful to you, and I look forward to your reply!

Best Regards, 
Thomas C - MSFT | Microsoft Community Support Specialist