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)