Formulas for golf tournament - assigning $$$ in separate cell and listing multiple names

Hello Excel Gurus.

I am so blessed that I came across this site. I have been lucky enough to use it several times to get answers to some what I feel are tougher than normal excel questions. I have thought of a couple more and any help by you experts would be fantastic.

I have attached a sample spreadsheet for your viewing pleasure. :-)

In Scenario 1, I have a list of players that were on the winning team for Day 1 of our annual golf tournament. Each player on the 15 man team wins money. The top 12 win $25 and the bottom 3 only win $10. In the case of a tie for the 3rd spot, they would split the higher and lower amounts. So if two guys tie, we would add the $25 and $10 and divide by 2 so they would each win $17.50 which we would round up to $18. If three guys tie, it would be $25+$25+$10 divided by 3 = $20 each and so on.

In Scenario 2, I would like to be able to show the player that has the lowest score on a hole or list all the players that tie for the lowest score on a hole. In some cases, there may be 20 guys that tie for the lowest score and they all won't be able to fit in the cell that I have designated for all the names. Is there a way to list as many names that can fit and then show something like "..." to indicate there are more names but they don't fit? I played in an event year's ago and I saw this but I am not sure how to make that work.

Thanks for all your help and I appreciate your time.

Mike

Try, copy down:
E1=LET(x,$B$1:$B$15,y,LARGE(x,3),z,COUNTIF(x,y),IF(B1=y,ROUNDUP((25*(z-1)+10)/z,0),IF(B1>=LARGE(x,2),10,25)))
G21=LET(x,FILTER($A$21:$A$35,XLOOKUP(F21,$B$20:$D$20,$B$21:$D$35)=MIN(XLOOKUP(F21,$B$20:$D$20,$B$21:$D$35))),TEXTJOIN(", ",TRUE,TAKE(x,5))&IF(COUNTA(x)>5," …",""))

WOW... I am amazed! Thanks so much, Josephteh. Both of these formulas work perfectly! I am grateful for you and this site. I am getting better and better at Excel and to have people like yourself that I can come to for answers is great. Please accept my sincere gratitude and I hope you have a wonderful week.

Oops. I jumped the gun on the formula for the first scenario. Your formula for what I was asking was correct but I didn't quite ask the question correctly. When assigning the winning dollar amount for each player, the column that the money needs to go into has all 120 participants listed so I need some sort of LOOKUP to make sure the money goes to the correct player. I have added an updated spreadsheet that shows what I am looking for. I am very sorry for the double work.

Try this formula:
=LET(x,$B$1:$B$15,y,LARGE(x,3),z,COUNTIF(x,y),IFERROR(IF(XLOOKUP(D1,$A$1:$A$15,$B$1:$B$15,NA())=y,ROUNDUP((25*(z-1)+10)/z,0),IF(XLOOKUP(D1,$A$1:$A$15,$B$1:$B$15,NA())>=LARGE(x,2),10,25)),0))

Hi there, Josephteh... Thanks for getting back to me. This formula is super close. The only issue now is that if there are only three players with the three highest scores, they should each only get $10. With this formula, the guy with the highest score is only getting $10 but the two players with the 2nd and 3rd highest score are each getting $18. They should also only get $10. There is only a split of the 25 and 10 if there are 4 or more players that have the three highest scores. I appreciate you taking the time to work this out for me.

Try, =LET(x,$B$1:$B$15,y,LARGE(x,3),z,COUNTIF(x,y),IFERROR(IF(AND(COUNTIF(x,">="&y)>=4,XLOOKUP(D1,$A$1:$A$15,$B$1:$B$15,NA())=y),ROUNDUP((25*(z-1)+10)/z,0),IF(XLOOKUP(D1,$A$1:$A$15,$B$1:$B$15,NA())>=LARGE(x,2),10,25)),0))

THAT'S IT! That's exactly what I was looking for. This is a great time saver for my event and I am so appreciative of your help, josephteh. You are a great person for coming on here and helping guys like myself. I have learned so much through being able to communicate with people from all over the world that are experts in Excel formulas. Thank you, thank you, thank you. I hope you have a wonderful year.

You are most welcome! Thanks for the Rep and for marking this thread Solved.