Author Topic: Help with a formula  (Read 5238 times)

DominicStokes

  • Newbie
  • *
  • Posts: 10
    • View Profile
Help with a formula
« on: July 05, 2013, 05:27:40 PM »
Can anyone suggest a formula that returns the lowest score out of the best 15 scores in a season? For Best 15 I'm using .... sum(top(15,scores)) although we are only on 13 games so fingers crossed it works come the time. Basically Any help would be appreciated.

ta Dom

Corey Cooper

  • Administrator
  • Hero Member
  • *****
  • Posts: 6216
    • View Profile
Re: Help with a formula
« Reply #1 on: July 05, 2013, 10:24:03 PM »
bottom(1, scores)

For best N scores, there are really two ways to do it.  Assuming, for example, you are taking the top 15 out of 16, top(15, scores) works just fine.  It means you're dropping the lowest score.  And that's another way to look at.  In this case you know there will be 16 tournaments.  If in one season you had 20, you'd have to change the formula to sum(top(19, scores)).  You're still dropping the lowest 1 score.  You could change it to sum(top(n - 1, scores))n is the number of tournaments, so this would always drop the lowest score no matter how many tournaments you have.  You wouldn't have to change it for a season in which you had a different number of tournaments.  However, it always drops a score, no matter how many tournaments you play or how far along the series (or season) you currently are, whereas sum(top(15, scores)) doesn't drop any scores until you've played at least 16 tournaments.

DominicStokes

  • Newbie
  • *
  • Posts: 10
    • View Profile
Re: Help with a formula
« Reply #2 on: July 08, 2013, 04:24:19 AM »
Thanks Corey that's reassuring. I want to be able to return the 15th best score so that players know how many points they will be drop and hence what they have to score to increase their  top 15 scores tally. Hope that makes sense.

I've come up with the following

index((top(15,scores)),14)   

what do you think? will it do what I want and return the 15th best score of a player?

Many thanks

Dom

Corey Cooper

  • Administrator
  • Hero Member
  • *****
  • Posts: 6216
    • View Profile
Re: Help with a formula
« Reply #3 on: July 08, 2013, 10:17:46 AM »
Unfortunately there's no guarantee as to the ordering of the scores (it won't necessarily be in played order, or in order from lowest to highest or highest to lowest), so you should avoid the index() function.

Instead use this: bottom(1, top(15, scores))

top(15, scores) keeps only the highest 15 scores.  Then bottom(1, ...) keeps only the lowest of those 15 scores.  Thus you end up with the 15th highest score, which is what I think you were after.

DominicStokes

  • Newbie
  • *
  • Posts: 10
    • View Profile
Re: Help with a formula
« Reply #4 on: July 19, 2013, 01:30:46 PM »
that's great thank you for your help :)

DominicStokes

  • Newbie
  • *
  • Posts: 10
    • View Profile
Re: Help with a formula
« Reply #5 on: July 19, 2013, 01:41:46 PM »
Ah slight problem. I have a person that has played 1 game and scored 12 points. The formula you have given me returns this score as their 15th best. It isn't their 15th best score it's their only score. Is there anyway to  return a zero until they have played at least 15 games?

cheers

Dom

DominicStokes

  • Newbie
  • *
  • Posts: 10
    • View Profile
Re: Help with a formula
« Reply #6 on: July 20, 2013, 03:52:03 AM »
Latest effort can you see any problems?

if(buyins<15,0,(bottom(1,top(15,scores))))


cheers

Dom

Corey Cooper

  • Administrator
  • Hero Member
  • *****
  • Posts: 6216
    • View Profile
Re: Help with a formula
« Reply #7 on: July 20, 2013, 09:16:45 PM »
Yep, that looks right, as long as you're using version 3.2.  If you're using an earlier version, you'll need to change buyins to count(scores):

if(count(scores)<15,0,(bottom(1,top(15,scores))))