Finding highest numbers again

Mike7

Board Regular
Joined
Dec 27, 2002
Messages
93
Hi.
So many questions about finding highest numbers, but all solutions I have found just returns them. I need it to be displayed as value 1 or 0 if non highest.
question.xls
ABCDEFGHIJKL
1
23425462375
35345873647
46445364234
5
60000010010
70000110001
81001010000
Sheet3


Question 1. See table. The source range is B2:K4, result range - B6:K8. I need to find any occurrences of first and second highest values in every row and get it as value 1 in result block like it done on my example.
Question 2. Like in first question but the rows are longer - from B to CW and contain 100 numbers. I need to find half highest of them and get it as value 1. Due to multiply occurrences of some numbers it cannot be always 50 and 50, so in such cases closest to 50 over 50 set must be chosen (51,52,53 etc).
Thanks.
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Mike,
for the first question
would this work for you ?
Code:
=--(RANK(B2,$B2:$K2)<3)
trying the second now

kind regards,
Erik

EDIT: formula in B6 copied down and accross
 

mdusoe

Active Member
Joined
Aug 27, 2004
Messages
428
For question 1, enter the following formula in B6:
=IF(OR(B2=MAX($B2:$K2),B2=MAX(($B2:$K2)*($B2:$K2 < MAX($B2:$K2)))),1,0)
Confirm with Ctrl+Shift+Enter, and copy to the rest of the area.

I am working on question 2...

HTH,
Mike.

edit: Erik, does that work if the Top number is duplicated? In that case, the second highest would be eliminated, right? Although maybe that is desired... Mike?
 

Mike7

Board Regular
Joined
Dec 27, 2002
Messages
93
It works for first two lines but in third line I got only first highest counted (6 and 6) and the second highest (5) is missed and displayed as 0.
Edited: I'm saying about the first try.
Thanks
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832

ADVERTISEMENT

edit: Erik, does that work if the Top number is duplicated? In that case, the second highest would be eliminated, right? Although maybe that is desired... Mike?
good point !!
which needs some clarification ...
 

mdusoe

Active Member
Joined
Aug 27, 2004
Messages
428
As for question 2, maybe try this formula:

=--(B2>=MEDIAN($B2:$CW2))

Does that work?
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832

ADVERTISEMENT

for the second, my suggestion would be
Code:
=--(RANK(B2,$B2:$K2)=RANK(LARGE($B2:$K2,5),$B$2:$K$2))
assuming we are still working on the same range, hence using "5" as the middle instead of 50
(if this works you can enlarge the range)
 

Mike7

Board Regular
Joined
Dec 27, 2002
Messages
93
The second formula for question1 works good.
For question2 works too, but I faced up not desirable result when closest to 50 lower set was 48 and highest set was 74 numbers. If it possible I would change criteria to " closest to 50 from any of both sides". But in cases like 48 vs 52, 47 vs 53 etc prefer over 50.
Thanks
 

Mike7

Board Regular
Joined
Dec 27, 2002
Messages
93
Second formula fo question2
.
Code:
=--(RANK(B2,$B2:$K2)=RANK(LARGE($B2:$K2,5),$B$2:$K$2))

I got cells with errors
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Mike7 said:
Second formula fo question2
.
Code:
=--(RANK(B2,$B2:$K2)=RANK(LARGE($B2:$K2,5),$B$2:$K$2))

I got cells with errors
please provide an example with data which triggers errors
 

Watch MrExcel Video

Forum statistics

Threads
1,118,735
Messages
5,573,916
Members
412,555
Latest member
mark84
Top