Need help with ranking numbers in a formula

[Billco]

I have the following golf scores for seven different teams. The best score wins $30.00 and the worst sco:

216
206
195
204
207
197
202

I would like to assign the lowest number (195) with a positive winning value of $30. The second lowest score of 197 would be assigned a winning value of $20. Third best score (202) gets $10 and the score in the middle (204) breaks even or gets $0.
The fifth lowest score (206) looses $10, the sixth looses $20, and the last place team looses $30. So it looks like this:

A1 A2
216 -$30
206 -$10
195 +$30
204 $0
207 -$20
197 +$20
202 +$10

I would like to be able to just enter the scores in A1, and have the spreadsheet assign the winnings or losings based on the order of finish. There will always be one team winning $30, and one losing $30, one team winning $20 and one losing $20, one team winning $10 and one loosing $10, and one team breaking even at zero.

Any help with the brain teaser (at least for me) would be appreciated.

Bill

[Crooza]

Try in b1 and copy down

= -40+rank(A1,$A$1:$A$7,0)*10

[Billco]

Thanks Crooza.

Im VERY impressed.

It works perfectly. Now I have to figure out why. :-)

Happy New Year!!!

[Crooza]

I'll break it down for you.



rank(A1,$A$1:$A$7,0) gives you the relative ranking for A1 in the array A1 to A7. In this case A1 is the highest or first ranked. As you want increments of 10 I multiplied each ranking by 10.

As you want the lowest value attributed to the highest rank I started with -40 and each rank adds it's relevant multiple of 10 to that starting number.

If you haven't already done so you should mark this as solved

[joe31623]

This should work; but let me know if you need to sort more than 7 players and I'll modify it. It does not require that you sort the rows in terms of who scored highest/lowest. The following code requires:
1: Scores are recorded in column 1
2: Scores begin in row 2 (and continue w/ no spaces)
3: Winnings are assigned column 2 (across from score)
4: There are 7 players and the max winning amt. is $30
5: The sheet is named "Sheet1" (change below if different)

Please Login or Register  to view this content.

[scottiex]

Note that if you wanted the formula (as the title suggests) you put this in the wrong section of the forum - there is an excel formulas and functions section.

If you actually think something like what Joe has done above is your best answer - then you put it in the right forum... but not with the right title!

[Billco]

Whoops.
This formula works perfectly unless there is a tie. Let me explain further.
The purpose of this formula is to allow an automatic calculation that will award each team money from each them they defeat. In my example, there are seven teams and each team wager is $5.00. Therefore, the team who has the lowest score, beats the other five teams, giving them a total of $30.00. The second place team has defeated five teams (winning $25) and loses to one team (losing $5) for a total winnings of $20.
The formal you presented works perfectly with the example scores I gave you, but if there is a tie, it does not correctly account for the results. For example, if team one and two tie for low score, the formula awards each team $20 when in fact, each team has defeated five other teams and should win $25.
Not sure if this makes sense but hopefully you can come up with a solution if there are ties.
Keep in mind. Every team you beat pays you $5 and you pay $5 to every team that beats you. Ties break even.
Thanks.

[Billco]

By the way. I am obviously new here and may have posted this in the wrong section. If the moderator would like to move it, that would be fine.

Bill

[jewelsharma]

try this:

Please Login or Register  to view this content.

[joe31623]

Billco,

This code should work if there is a duplicate score. I didn't debug it yet, but it may work. I just want to make sure you're attempting the solution if I work on it further. Let me know how the following works for you.

Please Login or Register  to view this content.

And please work to comply with rule 1 as described by the forum moderator above, else, I cannot in good faith help you any further. Because others may seek a similar issue and conforming to the rules helps us help more people so we do not need to re-work what has already been done.

[joe31623]

Perhaps a more accurate title would be: "Allocating a fixed amount to a dynamic set of partitions defined by Scores (or golf scores if enough room). The set of partitions is dynamic b/c if there are 7 separate scores, there are 7 partitions, if there is one tie, there are 6 partitions, if there is one threeway-tie there are 5 partitions, if there are two ties, there are 5 partitions, etc.

I don't think there is any way to do this with formulas. This thread is in the correct forum with a title that does not accurately describe your problem.