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"

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.

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

Re: okay, I've done that, but

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.

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.

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)

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:

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).