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

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Mike7 said:
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

Board Regular
Joined
Dec 27, 2002
Messages
93

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
perhaps I see what you mean
next rank is in fact "lower" so try SMALL instead of LARGE
Map1.xls
ABCDEFGHIJK
1LARGE4
2SMALL6
3
4data3230427225
5RANK46410361662
6
71010000000
80100010110
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832

ADVERTISEMENT

Mike7 said:
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
ABCDEFGHIJK
1LARGE3
2data3230427225
3
41010101001
kitchen


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

Mike7

Board Regular
Joined
Dec 27, 2002
Messages
93
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:
Mike7 said:
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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

Board Regular
Joined
Dec 27, 2002
Messages
93
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 :biggrin: ) 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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,017
Messages
5,599,362
Members
414,306
Latest member
Dennis_vdw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top