Highest number...TIE?

[Jesse]

I figured out how to display the higest number from
several numbers and display the winner ( For my football
pool) using NESTED IF
statements......but now my problem is what if there is a
tie???? ANy suggestions??
I would like it to say ROLLOVER...but how can I
accomplish this with my nested IF statements?? Or do I
have to kill those statements and go a different direction

 

[Bob Phillips]

Jesse,

Why are you using Nested IFs? I would find the highest number with
=MAX(A1:A20)
say.

You can check if there is more than 1 with
=COUNTIF(A1:A20,MAX(A1:A20))
This will return > 1 if more than 1.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Ron Rosenfeld]

I figured out how to display the higest number from
several numbers and display the winner ( For my football
pool) using NESTED IF
statements......but now my problem is what if there is a
tie???? ANy suggestions??
I would like it to say ROLLOVER...but how can I
accomplish this with my nested IF statements?? Or do I
have to kill those statements and go a different direction

I would do something like this:

=IF(COUNTIF(Scores,MAX(Scores))>1,"ROLLOVER",
"Max Score is "&MAX(Scores) & " by " &
INDEX(Players,MATCH(TRUE,Scores=MAX(Scores),FALSE)))

The formula must be *array-entered*. That is to say, hold down <ctrl><shift>
while hitting <enter>. XL will place braces {...} around the formula.

Players is the column which contains your list of players.
Scores is the column which contains the corresponding scores.

The formula will need modification if your setup is dissimilar.


--ron

 

[Jesse]

Well I used IFs cause that the only way I knew how....I
see your guys suggestion...although I dont exactly follow
it, Im sure now that you gave that to me Ill figure it
out...thanks