formula to assign points with an upper limit

[BethaW]

Hello,
I need to assign points to the top 6 placings in a horse show class but I need an upper limit. If there are 5 horses in the class the first place rider gets 5 points, 2nd place gets 4, 3rd gets 3, 4th gets 2, etc. If there are only 3 horses first gets 3, second 2, third 1, etc. However, once there are 6 or more riders the first place gets 6, second 5, third 4, etc. regardless if there are 6 or 100 horses in the class.

Col A lists the riders in the class
Col G has an "x" if they want their points calculated or "n" if they don't
Col J is their class placing...1 thru 6

currently I have the below formula which works great except it keeps going so that if there are 8 horses in the class it gives the first place 8 points and I need it capped at 6.

=IF(AND(G3="x",J3>0),-J3+COUNT($A$3:$A$30)+1,(""))

Thank you.

 

[Mackers]

Try changing the +COUNT($A$3:$A$30)+1 to + MAX(MIN(COUNT($A$3:$A$30), 5)+1, 0)

Not sure if that 5 is right but have a play around. I think you want to count the members until it's 6 max, but make sure if there are 8 people they go 6,5,4,3,2,1,0,0? I haven't tested this so let me know if it doesn't work!

Mackers

 

[BethaW]

Works like a charm! Thx!!!!