Finding highest numbers again

[Mike7]

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
	A	B	C	D	E	F	G	H	I	J	K	L
1
2		3	4	2	5	4	6	2	3	7	5
3		5	3	4	5	8	7	3	6	4	7
4		6	4	4	5	3	6	4	2	3	4
5
6		0	0	0	0	0	1	0	0	1	0
7		0	0	0	0	1	1	0	0	0	1
8		1	0	0	1	0	1	0	0	0	0
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.

 

[erik.van.geit]

Mike,
for the first question
would this work for you ?

=--(RANK(B2,$B2:$K2)<3)

trying the second now

kind regards,
Erik

EDIT: formula in B6 copied down and accross

 

[mdusoe]

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]

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]

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]

As for question 2, maybe try this formula:

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

Does that work?

 

[erik.van.geit]

for the second, my suggestion would be

=--(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]

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]

Second formula fo question2
.

=--(RANK(B2,$B2:$K2)=RANK(LARGE($B2:$K2,5),$B$2:$K$2))

I got cells with errors

 

[erik.van.geit]

Second formula fo question2
.

=--(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