Need an alternative to lots of nested IF statements

I originally posted this attachment in Change a cell in a range and all other cells in the range change to keep the sum at 100% I don't think that this post should be part of the original because they are completely different problems. Moderators please let me know otherwise.

While only one of the issues I was trying to accomplish at the time was addressed in the previous post, that of keeping the sum of a range of cells always equal to 100%, I ended up not using the suggestion and figured that part out on my own because I needed the percentages to be top heavy so the payout would get better the better a team placed.

The part I'm having a problem with now is dealing with the end of league place standings in the case of a tie between two or three teams and with the possibility of there being more than two teams for any given place and/or more than two teams tied for different placings. I started to figure it out (I thought) but realized that each placing down in the standings, 1st to 2nd to 3rd, etc., would require an increasingly longer nested IF statement to be made and it's unbelievably confusing. See the example below of just 1st through 4th.

1 1
2 IF(D22=D23,1,2)
3 IF(AND(D22>D23,D23>D24),3,IF(AND(D22>D23,D23=D24),2,IF(AND(D22=D23,D23=D24),1,2)))
4 IF(AND(D22>D23,D23>D24,D24>D25),4,IF(AND(D22=D23,D23>D24,D24>D25),3,IF(AND(D23>D24,D24=D25),3,IF(AND(D23=D24,D24=D25),2,IF(AND(D22=D23,D23=D24),2,3)))))

By the time I get to 24th place this statement will be about 10 miles long.

Open the attachment and start messing around with the "Total Games Won This Season" column (D22:D45) by changing the scores to and from ties. I gave up at 4th place as shown above. Is there an easier way to make it so the "Place (with ties)" column (B22:B45) will automatically change the place numbers to accommodate for teams being tied at the end for multiple places and the possibility of more than just two teams tied for any specific placing?

Let me know if you need more explanation.

Thanks!

sounds like you are trying to do something requiring the 'Rank' function

give this a shot and see if thats what you are looking for.

=RANK(D22,D$22:D$45)

Hi DG. Thanks for the suggestion. It almost works. I pasted it into D22 and copied down. It isn't handling tied teams though. If I set the top two teams to be tied, it keeps D22 as 1 and changes D23 to 1 but doesn't affect the rest of the cells from D24:D45. They remain as they were. D24:D45 should change from 3 through 24 to 2 though 23. If there were a three way tie between the top three teams, they would each be 1 and D25 (the 4th team listed would carry on as 2. If then there was a tie for 3rd between the 6th and 7th teams listed (D27 and D28) they would both be listed as 3 and then D29:D45 would be 4 through 21.

you are right, not sure how i missed, that

try this out

=RANK(D22,D$22:D$45)+SUMPRODUCT(--((D$22:D$45>D$22)))

That does the same thing. See below how they line up. 1, 1, skip 2, 3 and again at 7 and thirteen.

1 1
2 1
3 3
4 4
5 5
6 6
7 7
8 7
9 9
10 10
11 11
12 12
13 13
14 13
15 13
16 16
17 17
18 18
19 19
20 20
21 21
22 22
23 23
24 24

I was looking at something I found just now and it works like yours but arranges them in the opposite order from 24 down to 1 and it also skips numbers. The formula is =RANK(D22,$D$22:$D$45,1)+COUNTIF($D$22:$D$45,D22)-1

I found it. =SUM(IF(D22<$D$22:$D$45,1/COUNTIF($D$22:$D$45,$D$22:$D$45)))+1 Ctrl+Shift+Enter

It was at http://www.excelforum.com/excel-work...s-in-rank.html

Thanks for the help! You got me searching differently.

i think im getting the desired result, see the attached image and let me know where the wrong numbers are.

untitled.JPG

Look at this. You'll understand when you see it.

Thanks again for your help.

ahh ok, thank you for share, i definatly misunderstood the goal.

must be friday