Find first row that meets a criteria and give bonus

[mooserken]

I’m trying to figure out how to do something in Libreoffice Calc. What I’m trying to do is give a player bonus points if they are the first to reach a certain number. Once the bonus points are given, then I want this feature to be “turned off” as other players will reach this certain number. Unfortunately, I have not figured out how to do this and am hoping that you could help. I have attached a sample spreadsheet for your referal. I will describe it.

It is for racing stats. I have 20 racers. They do 15 races and in each race the top 10 racers are placed: first, second, third, etc. What I want is that the first person to reach a certain Target Number based on their placements get a bonus. For example, racer 1 gets first place, that racer gets 1 towards the target number. Racer 2 gets second place, that racer gets 2 towards the target number. As you can see on the spreadsheet the Target Number is 22 and the Bonus is 50pts. When the first player (Racer I) reaches the target number (22pts), then that racer gets the bonus of 50pts. You can see that Racer I has a total of 25 after 3 races, therefore racer I gets the 50 point bonus. The placement number is a running total for each racer more than one racer will get a placement number greater than or equal to the Target Number. So once the bonus is given the first time I don’t want the bonus to be given out again. As you can see, racer P got the target number of 22 on race 4 but does not get the 50 point bonus.

How can I achieve this? I’ve looked at HLookup, VLookup, array formulas, Match and other functions but I haven’t been able to figure it out. I hope someone out there can help me. Thank you to all who take the time to help.

Calc Spreadsheet below
Stats Help.ods (12.1 KB)

[gregors15]

Have a look at the revised attached file, bonus automatically applied to winning rider, all the calculations/workings out are on sheet 2.
LOQ20220516_StatsHelp.ods (21.7 KB)

[mooserken]

Hi @gregors15. Thanks for the spreadsheet revision. It is what I’m looking for and believe me, I would never in a million years been able to get to this stage, so thank you. As I was working through what you have done, I noticed one thing. When multiple riders hit the bonus target in the same race, the bonus amount will be given to the last racer who hit the target. Somehow the first bonus rider winner (Sheets2:B32) needs to be locked in so that it cannot be changed. One thing that I have thought of is taking the lower number in Column V in Sheets2 and add that into the formula for Sheets2:B32 to force B32 to save the first rider. Other than that, I am very impressed with your work. Thank you for taking the time to help me out. I really appreciate it.

[gregors15]

Hi mooserken,

Have a look at the attached, I have added a total to the bonus column (V23), and then used conditional formatting to turn the cell(s) red in column(U2.U21) should the running total be equal to, or greater than the bonus target number. You just need to enter the bonus amount into column V.

What would you do if after the same number of races, 2 or more riders had the same number of points, i.e. say after 5 races 3 riders had totalled 25 points.

Produced in LO 7.3.3.2, windows 10 home.

Let us know if it helps.

LOQ20220516_StatsHelp.ods (12.8 KB)