A list of the 8 highest results...

  • Thread starter Thread starter JemyM
  • Start date Start date
J

JemyM

I have a stylesheet that contains "knowledges".
The list looks something like:

Knowledges:
Accounting 14
Anthropology 25
Archeology 48
Art 35
Astronomy 59
Biology 124
Chemistry 123
Geology 15
History 28
Law 167
Medicine 69
Natural History 89
Pharmacy 198
Physics 20

Note the value next to each "knowledge". This number is a calculated
formula that results in a number between 1-200.

Now, if it's possible, I would like Excel to produce a separate list of
the eight knowledges with the highest values right next to it.

Pharmacy
Biology
Chemistry
etc...

Any way this can be done?

Thanks for all replies, if any.
 
One way, using non-array formulas,
which caters for the possibility of ties in the numbers ..

Assuming table in cols A and B, data from row2 down

Put in D2:
=INDEX(A:A,MATCH(LARGE($F:$F,ROWS($A$1:A1)),$F:$F,0))
Copy D2 across to E2

Put in F2: =IF(B2="","",B2-ROW()/10^10)
(Leave F1 empty)

Select D2:F2, copy down to F15

Cols D and E will return the full descending sort of what's in cols A and B
(Col F is the arbitrary tie-breaker)

Just select the desired top 8 from the list within cols D and E
 
A slightly simpler way, that also allows you to stop at 8

In D2: =INDEX($A$2:$A$15,MATCH(LARGE($B$2:$B$15,ROW(A1)),$B$2:$B$15,0))

and copy down as far as you want

--
HTH

Bob Phillips

Max said:
One way, using non-array formulas,
which caters for the possibility of ties in the numbers ..

Assuming table in cols A and B, data from row2 down

Put in D2:
=INDEX(A:A,MATCH(LARGE($F:$F,ROWS($A$1:A1)),$F:$F,0))
Copy D2 across to E2

Put in F2: =IF(B2="","",B2-ROW()/10^10)
(Leave F1 empty)

Select D2:F2, copy down to F15

Cols D and E will return the full descending sort of what's in cols A and B
(Col F is the arbitrary tie-breaker)

Just select the desired top 8 from the list within cols D and E

--
Click to expand...
 
Another option is to simply copy cols A and B elsewhere and sort by the
values (descending).
 
Max said:
One way, using non-array formulas,
which caters for the possibility of ties in the numbers ..

Assuming table in cols A and B, data from row2 down

Put in D2:
=INDEX(A:A,MATCH(LARGE($F:$F,ROWS($A$1:A1)),$F:$F,0))
Copy D2 across to E2

Put in F2: =IF(B2="","",B2-ROW()/10^10)
(Leave F1 empty)

Select D2:F2, copy down to F15

Cols D and E will return the full descending sort of what's in cols A
and B
(Col F is the arbitrary tie-breaker)

Just select the desired top 8 from the list within cols D and E
Click to expand...

Since the list is sensitive for ties I have tried to go by this
version.

After alot of work I finally got it to work...

My major issue was that I was forced to translate the whole thing to
Swedish, and I did not see the difference between ROW and ROWS at
first. I also managed to write the swedish word for "LARGER" instead of
"LARGE" which caused alot of confusion.

It works now and I can therefore continue my work :) Thanks alot!
 
Back
Top