I'm close ... Comparing values and returning a conditional response

Posted by Chuck on March 20, 2001 10:14 AM

Hi. First time poster, and I'm a bit nervous, but what a wonderful resource!

I'm trying to compare a group of 8 numbers, from g3..n3 (and g4..n4, and so on) and then print out a phrase depending on which column has the highest number.

If two of the numbers between g3..n3 are the same, and they are the two highest numbers, I would like to print the word "TIE"

I came close, I think, as you will see if you follow the link, but if the two highest numbers are the same, it doesn't print "TIE"

I would appreciate any advice.

Thanks,
Chuck

Posted by Aladin Akyurek on March 20, 2001 10:48 AM

You could add an addtional column after the one where you determine the MAX values per row, and use the followin formula in G3:

=IF(COUNTIF(H3:O3,F3)>1,"TIE","")

and copy this down as far as needed.

Aladin

Posted by Chuck on March 20, 2001 11:49 AM

Re: okay, I've done that, but

Hey, thanks Aladin, for addressing my problem.

I've done what you've said, and it works like a charm. I even added something that helps bring about the result I want.

Instead of: =IF(COUNTIF(H3:O3,F3)>1,"TIE","")

I've added the cell reference between the last two quotation marks, so instead of spitting out nothing, it spits out the value I want associated with the highest number.

I wonder if there is a way to combine these, though, because I have a column sitting out there doing nothing but providing a formula for another column to use. I guess I can hide it.

Your suggestion greatly helped, though.

Thanks, again.
Chuck

Posted by Scott R on March 20, 2001 1:19 PM

In cell O3:
=IF(COUNTIF(G3:N3,F3)>1,"TIE",OFFSET(F$2,,MATCH(F3,G3:N3,0)))

Posted by Chuck on March 20, 2001 1:35 PM

Re: Worked like a charm -- Thanks! (nt)

Posted by Mark W. on March 20, 2001 1:56 PM

Chuck, I thought you wanted to compare the values
in columns G:H to each other? This formula
does that:

=IF(SUM(LARGE(G3:N3,{1,2})*{1,-1}),CELL("address",INDEX(G3:N3,MATCH(LARGE(G3:N3,1),G3:N3,0))),"TIE")

Posted by Mark W. on March 20, 2001 1:57 PM

Oops! Typo.

Posted by Aladin Akyurek on March 20, 2001 3:30 PM

A fancy proposal (Re: okay, I've done that, but)

I was off-line and, more importantly, you've got further help. What follows is a fancy proposal to play with:

In F3 enter and copy down: =MAX(H3:O3)&" "&IF(COUNTIF(H3:O3,""&MAX(H3:O3))>1,"TIE","")

in case you do not use the values in further computations (or don't mind complicated formulas for further processing).

Aladin