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.

 

[Mike7]

I'm sorry I missed that you narrowed the range
Still trying

 

[erik.van.geit]

I'm sorry I missed that you narrowed the range
Still trying

yes, that's easier to work on
my advise would be to stick to the small range here
to enlarge the range is not at all the problem

 

[Mike7]

I got wrong results

Main.xls
	B	C	D	E	F	G	H	I	J	K
2	3	2	3	0	4	2	7	2	2	5
3	2	3	1	0	2	0	4	3	1	2
4	2	4	2	0	1	2	2	5	1	2
5	2	5	4	0	5	5	1	4	4	2
6	5	1	4	0	1	3	2	2	2	4
7
8	1	0	1	0	0	0	0	0	0	0
Sheet8

 

[erik.van.geit]

what's wrong ?
what did you expect ?

 

[Mike7]

I expected to find the half highest numbers. So in range B2:K2 which contains 10 numbers I need to find 5 highest. On my table they are in B2,D2,F2,H2,K2 cells, but as value 1 I got displayed only B and D.

 

[erik.van.geit]

perhaps I see what you mean
next rank is in fact "lower" so try SMALL instead of LARGE

Map1.xls
	A	B	C	D	E	F	G	H	I	J	K
1		LARGE	4
2		SMALL	6
3
4	data	3	2	3	0	4	2	7	2	2	5
5	RANK	4	6	4	10	3	6	1	6	6	2
6
7		1	0	1	0	0	0	0	0	0	0
8		0	1	0	0	0	1	0	1	1	0
kitchen

to make the entire sheet less "expensive" in calculation, you can put this part of the formulas (same in each formula)
RANK(LARGE($B2:$K2,5),$B$2:$K$2))
in cell C1
and then use
B7: =--(RANK(B4,$B4:$K4)=$C$1)
in cell C2 there is
=RANK(SMALL($B4:$K4,5),$B$4:$K$4)
B8: =--(RANK(B4,$B4:$K4)=$C$2)

would this do what you need ?
best regards,
Erik
(leaving in a couple of minutes)

 

[erik.van.geit]

I expected to find the half highest numbers. So in range B2:K2 which contains 10 numbers I need to find 5 highest. On my table they are in B2,D2,F2,H2,K2 cells, but as value 1 I got displayed only B and D.

OK
forget my previous post, except for the fact you can put part of the formula in one cell

C1: =LARGE($B2:$K2,5)
B4: =--(B2>=$C$1)
or is this to easy ?

Map1.xls
	A	B	C	D	E	F	G	H	I	J	K
1		LARGE	3
2	data	3	2	3	0	4	2	7	2	2	5
3
4		1	0	1	0	1	0	1	0	0	1
kitchen

kind regards,
Erik
(offline now for at least 4 hours)

 

[Mike7]

Thanks Erik.
I put part of the formula for every row and it works good. But I still have not desirable thing occured which I described earler:

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

I'd like the formula do like this: 100 cells contains 100 numbers - 2(24), 3(11), 4(16), 5(20), 6(12), 7(17). In this case I prefer to get 49 (less than 50) highest numbers (7,6,5)as value 1 instead of 65 highest numbers (7,6,5,4).
I'd be glad to get any ideas.
Thanks.

 

[erik.van.geit]

OK, I see what you mean now
tried for more than an hour to no avail
all kinds of sumproducts, Median, Large, etcetera

I can only imagine a formulasolution with a lot of helpercells listing all values, counting, comparing, etcetera
when you need this for several rows it would involve a lot of extra cells

a PM to Aladin Akyurek or Fairwinds could help

if you don't find a formulasolution, I'm sure VBA is possible

I always wonder why people want this kind of things
can you explain a bit what your goal is ? perhaps this would lead to another solution

kind regards,
Erik

 

[Mike7]

Never mind Erik.
I tried it first in some random worksheet I found in my "internet computer" where the range had to many the same numbers because the highest number in the range was 7. But later I tried it on my "virgin computer" (never was connected to the internet ) where I do my projects. Actually the ranges contain higher numbers, like from 4 to 18, what gives less amount of repeated numbers. After many tries I got only sets with 51-56 numbers. So I fully satisfied with your solution.
Many thanks.