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

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

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,122,352
Messages
5,595,655
Members
414,006
Latest member
Davefromlondon

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