Nesting if/or/and

[newdeas]

I am counting scores to determine what place they are in. If the number
equals someone else's score then I want the result to read "Tie" if the
number is not a duplicate I would like it to read 1, 2, 3, & so on. However,
because my formula is to add 1 to A7, if A7 is a tie I get an error. Does
anyone have a suggestion (see my formula below):
=IF(OR(I8=$I$7,I8=$I$9,I8=$I$10,I8=$I$11,I8=$I$12,I8=$I$13,I8=$I$14,I8=I$15,I8=I$16,I8=I$17,I8=I$18,I8=$I$19,I8=$I$20,I8=$I$21,I8=$I$22,I8=$I$23,I8=$I$24,I8=$I$25,I8=$I$26,I8=$I$27,I8=$I$28,I8=$I$29,I8=$I$30,I8=$I$31,I8=$I$32,I8=$I$33,I8=$I$34,I8=$I$35,I8=$I$36,I8=$I$37),"TIE",A7+1)

 

[Dave]

Hi,
Not sure if I fully understand, but here's a suggestion -
I think I'd use COUNTIF to determine the ties. eg:
=IF(COUNTIF(I7:I37,I8)>1,"Tie","")
Then use RANK to determine the place, eg:
=RANK(I8,I7:I37)
Combination would be:
=IF(COUNTIF(I7:I37,I8)>1,"Tie",RANK(I8,I7:I37))
Is this close to what you're after?
Regards - Dave.

 

[John C]

To do exactly as stated:
=IF(COUNTIF($I$7:$I$37,I8)=1,RANK(I8,$I$7:$I$37),"Tie")

If you want to get a little fancier, you could try:

=IF(COUNTIF($I$7:$I$37,I8)=1,RANK(I8,$I$7:$I$37),"Tie - "&RANK(I8,$I$7:$I$37))

Both formulas you can copy up to cell J7, all the way down to J37.

 

[John C]

FYI, my solution means you do not need to put them in order any more, or to
add the A7+1, and so forth.

 

[newdeas]

Thanks! I'm soooo glad I came here I've been working on this for days