Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Highest, Second Highest , Third Highest and so on
Hi,
Can someone help me with the following please ? Example: A B C D E F G H I J K L 1 Chairs 100 Desk 50 Perfume 25 2 Tables 75 AC 75 Computer 25 3 Stapler 50 Doors 50 Bench 50 4 Telephone 25 Pipes 75 Fan 100 5 Mobile 0 Frame 100 Disk 100 I need to find out the highest, second highest and third highest from the above ( A1 : F1, A2:F2 , A3: F3 and so on.....) display in G H I J K L. The solution example for the above should be: G H I J K L 1 Chairs 100 Desk 50 Perfume 25 2 Tables 75 AC 75 Computer 25 3 Stapler 50 Doors 50 Bench 50 4 Fan 100 Pipes 75 Telephone 25 5 Disk 100 Frame 100 Mobile 0 ( Please notice the change in G4: L4 & G5:L5) Hope I you can understand the above lengthy stuff..... Thanks & Regards gkb |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Highest, Second Highest , Third Highest and so on
G1: =INDEX($A$1:$E$1,MATCH(H1,$B$1:$F$1,0))
H1: = LARGE($A$1:$F$1,1) I1: =INDEX($A$1:$E$1,MATCH(J1,$B$1:$F$1,0)) J1: = LARGE($A$1:$F$1,2) K1: =INDEX($A$1:$E$1,MATCH(L1,$B$1:$F$1,0)) L1: = LARGE($A$1:$F$1,3) -- --- HTH Bob (change the xxxx to gmail if mailing direct) "gkb" wrote in message ... Hi, Can someone help me with the following please ? Example: A B C D E F G H I J K L 1 Chairs 100 Desk 50 Perfume 25 2 Tables 75 AC 75 Computer 25 3 Stapler 50 Doors 50 Bench 50 4 Telephone 25 Pipes 75 Fan 100 5 Mobile 0 Frame 100 Disk 100 I need to find out the highest, second highest and third highest from the above ( A1 : F1, A2:F2 , A3: F3 and so on.....) display in G H I J K L. The solution example for the above should be: G H I J K L 1 Chairs 100 Desk 50 Perfume 25 2 Tables 75 AC 75 Computer 25 3 Stapler 50 Doors 50 Bench 50 4 Fan 100 Pipes 75 Telephone 25 5 Disk 100 Frame 100 Mobile 0 ( Please notice the change in G4: L4 & G5:L5) Hope I you can understand the above lengthy stuff..... Thanks & Regards gkb |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Highest, Second Highest , Third Highest and so on
Hi Bob,
Seems this is working. I am still applying this formula into a large worksheet. But before that I thought I should not miss say Thank you. Regards gkb "Bob Phillips" wrote: G1: =INDEX($A$1:$E$1,MATCH(H1,$B$1:$F$1,0)) H1: = LARGE($A$1:$F$1,1) I1: =INDEX($A$1:$E$1,MATCH(J1,$B$1:$F$1,0)) J1: = LARGE($A$1:$F$1,2) K1: =INDEX($A$1:$E$1,MATCH(L1,$B$1:$F$1,0)) L1: = LARGE($A$1:$F$1,3) -- --- HTH Bob (change the xxxx to gmail if mailing direct) "gkb" wrote in message ... Hi, Can someone help me with the following please ? Example: A B C D E F G H I J K L 1 Chairs 100 Desk 50 Perfume 25 2 Tables 75 AC 75 Computer 25 3 Stapler 50 Doors 50 Bench 50 4 Telephone 25 Pipes 75 Fan 100 5 Mobile 0 Frame 100 Disk 100 I need to find out the highest, second highest and third highest from the above ( A1 : F1, A2:F2 , A3: F3 and so on.....) display in G H I J K L. The solution example for the above should be: G H I J K L 1 Chairs 100 Desk 50 Perfume 25 2 Tables 75 AC 75 Computer 25 3 Stapler 50 Doors 50 Bench 50 4 Fan 100 Pipes 75 Telephone 25 5 Disk 100 Frame 100 Mobile 0 ( Please notice the change in G4: L4 & G5:L5) Hope I you can understand the above lengthy stuff..... Thanks & Regards gkb |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Highest, Second Highest , Third Highest and so on
Hi Bob,
Unfortunately LARGE does not work if there are identical values. In the third row you get STAPLES three times... I suggest to enter into G1:O1 =RANK($B1,$B1:$F1) =RANK($D1,$B1:$F1)+COUNTIF($B1,$D1) =RANK($F1,$B1:$F1)+COUNTIF($B1:$D1,$F1) =INDEX($A1:$F1,MATCH(1,$G1:$I1,)*2-1) =INDEX($A1:$F1,MATCH(1,$G1:$I1,)*2) =INDEX($A1:$F1,MATCH(2,$G1:$I1,)*2-1) =INDEX($A1:$F1,MATCH(2,$G1:$I1,)*2) =INDEX($A1:$F1,MATCH(3,$G1:$I1,)*2-1) =INDEX($A1:$F1,MATCH(3,$G1:$I1,)*2) then copy down as far as required. Regards, Bernd |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Display the Highest, Second Highest, Third Highest and so on... | Excel Discussion (Misc queries) | |||
Help! How do you get excel to find the x(changes daily, marked in a cell from another formula) highest numbers in a group of numbers and sum them up? | Excel Worksheet Functions | |||
Help! How do you get excel to find the x(changes daily, marked in a cell from another formula) highest numbers in a group of numbers and sum them up? | Excel Worksheet Functions | |||
Highest Value / Circular Reference Help | Excel Discussion (Misc queries) | |||
Highest Value / Circular Reference Help | Excel Worksheet Functions |