| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Display the Highest, Second Highest, Third Highest and so on...
Hi
Can someone help me to find out and place the highest, second highest, third highest and so on in group of numbers in a sheet? For example: QUESTION: A B C D E F 1004 1004 1004 1518 1517 797 1332 1320 1500 1337 1337 1004 1337 1337 1000 1940 560 1560 I need to find out which is highest, second highest and third highest from A,B & C and the D, E & F columns should display Highest, Second Highest, Third Highest accordingly. Where the numbers are equal, i need a display Equal(AB) or Equal(BC) or Equal (ABC) as the case depends. I might be overthinking on this, but I am just not getting the right formula. If someone could please help on this... please GKB |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Display the Highest, Second Highest, Third Highest and so on...
Use the LARGE() function.
-- Gary's Student "gkb" wrote: Hi Can someone help me to find out and place the highest, second highest, third highest and so on in group of numbers in a sheet? For example: QUESTION: A B C D E F 1004 1004 1004 1518 1517 797 1332 1320 1500 1337 1337 1004 1337 1337 1000 1940 560 1560 I need to find out which is highest, second highest and third highest from A,B & C and the D, E & F columns should display Highest, Second Highest, Third Highest accordingly. Where the numbers are equal, i need a display Equal(AB) or Equal(BC) or Equal (ABC) as the case depends. I might be overthinking on this, but I am just not getting the right formula. If someone could please help on this... please GKB |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Display the Highest, Second Highest, Third Highest and so on..
Hi,
Thank you. I did try this formula... and got the desired result. But I am not able to paste this formual in all cells. There is some "array" formula working here... =LARGE($A$1:$C$1,1) Hope you will get me a solution to this also. Thank you once again gkb "Gary''s Student" wrote: Use the LARGE() function. -- Gary's Student "gkb" wrote: Hi Can someone help me to find out and place the highest, second highest, third highest and so on in group of numbers in a sheet? For example: QUESTION: A B C D E F 1004 1004 1004 1518 1517 797 1332 1320 1500 1337 1337 1004 1337 1337 1000 1940 560 1560 I need to find out which is highest, second highest and third highest from A,B & C and the D, E & F columns should display Highest, Second Highest, Third Highest accordingly. Where the numbers are equal, i need a display Equal(AB) or Equal(BC) or Equal (ABC) as the case depends. I might be overthinking on this, but I am just not getting the right formula. If someone could please help on this... please GKB |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Display the Highest, Second Highest, Third Highest and so on..
"gkb" wrote:
.. But I am not able to paste this formula in all cells .. =LARGE($A$1:$C$1,1) Try it this way .. Assuming data in cols A to C from row1 down Put in D1: =LARGE(A:A,ROW(A1)) Copy D1 across to F1, then fill down as far as required -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Display the Highest, Second Highest, Third Highest and so on...
Hi Gary,
On its own, the LARGE() function returns the nth largest value (where n is the second argument in the function), not the column reference. Assuming you want the column references in all cases and A1 is your top-left data row: In Cell D1, use: =IF(LARGE($A1:$C1,1)=LARGE($A1:$C1,3),"Equal (ABC)",IF(LARGE($A1:$C1,1)=LARGE($A1:$C1,2),"Equal ("&CHAR(64+MATCH(LARGE($A1:$C1,1),$A1:$C1,0))&CHAR (64+MATCH(LARGE($A1:$C1,1) ,$A1:$C1,1))&")",CHAR(64+MATCH(LARGE($A1:$C1,1),$A 1:$C1,0)))) In Cell E1, use: =IF(LARGE($A1:$C1,1)=LARGE($A1:$C1,3),"Equal (ABC)",IF(LARGE($A1:$C1,1)=LARGE($A1:$C1,2),"Equal ("&CHAR(64+MATCH(LARGE($A1:$C1,1),$A1:$C1,0))&CHAR (64+MATCH(LARGE($A1:$C1,1) ,$A1:$C1,1))&")",IF(LARGE($A1:$C1,2)=LARGE($A1:$C1 ,3),"Equal ("&CHAR(64+MATCH(LARGE($A1:$C1,2),$A1:$C1,0))&CHAR (64+MATCH(LARGE($A1:$C1,3) ,$A1:$C1,1))&")",CHAR(64+MATCH(LARGE($A1:$C1,2),$A 1:$C1,0))))) In Cell F1, use: =IF(LARGE($A1:$C1,1)=LARGE($A1:$C1,3),"Equal (ABC)",IF(LARGE($A1:$C1,2)=LARGE($A1:$C1,3),"Equal ("&CHAR(64+MATCH(LARGE($A1:$C1,2),$A1:$C1,0))&CHAR (64+MATCH(LARGE($A1:$C1,3) ,$A1:$C1,1))&")",CHAR(64+MATCH(LARGE($A1:$C1,3),$A 1:$C1,0)))) Copy down as far as needed. Cheers -- macropod [MVP - Microsoft Word] "gkb" wrote in message ... Hi Can someone help me to find out and place the highest, second highest, third highest and so on in group of numbers in a sheet? For example: QUESTION: A B C D E F 1004 1004 1004 1518 1517 797 1332 1320 1500 1337 1337 1004 1337 1337 1000 1940 560 1560 I need to find out which is highest, second highest and third highest from A,B & C and the D, E & F columns should display Highest, Second Highest, Third Highest accordingly. Where the numbers are equal, i need a display Equal(AB) or Equal(BC) or Equal (ABC) as the case depends. I might be overthinking on this, but I am just not getting the right formula. If someone could please help on this... please GKB |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Display the Highest, Second Highest, Third Highest and so on..
try
=LARGE($A1:$C1,column()-3) "gkb" wrote: Hi, Thank you. I did try this formula... and got the desired result. But I am not able to paste this formual in all cells. There is some "array" formula working here... =LARGE($A$1:$C$1,1) Hope you will get me a solution to this also. Thank you once again gkb "Gary''s Student" wrote: Use the LARGE() function. -- Gary's Student "gkb" wrote: Hi Can someone help me to find out and place the highest, second highest, third highest and so on in group of numbers in a sheet? For example: QUESTION: A B C D E F 1004 1004 1004 1518 1517 797 1332 1320 1500 1337 1337 1004 1337 1337 1000 1940 560 1560 I need to find out which is highest, second highest and third highest from A,B & C and the D, E & F columns should display Highest, Second Highest, Third Highest accordingly. Where the numbers are equal, i need a display Equal(AB) or Equal(BC) or Equal (ABC) as the case depends. I might be overthinking on this, but I am just not getting the right formula. If someone could please help on this... please GKB |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Display the Highest, Second Highest, Third Highest and so on..
I pasted
=LARGE($A1:$C1,1) in D1 =LARGE($A1:$C1,2) in E1 =LARGE($A1:$C1,3) in F1 I Selected D1 through F1 and filled down for 6 rows. It did what I think you want except for the equals thing. I think that can be handled with an if statement. Lou "macropod" wrote: Hi Gary, On its own, the LARGE() function returns the nth largest value (where n is the second argument in the function), not the column reference. Assuming you want the column references in all cases and A1 is your top-left data row: In Cell D1, use: =IF(LARGE($A1:$C1,1)=LARGE($A1:$C1,3),"Equal (ABC)",IF(LARGE($A1:$C1,1)=LARGE($A1:$C1,2),"Equal ("&CHAR(64+MATCH(LARGE($A1:$C1,1),$A1:$C1,0))&CHAR (64+MATCH(LARGE($A1:$C1,1) ,$A1:$C1,1))&")",CHAR(64+MATCH(LARGE($A1:$C1,1),$A 1:$C1,0)))) In Cell E1, use: =IF(LARGE($A1:$C1,1)=LARGE($A1:$C1,3),"Equal (ABC)",IF(LARGE($A1:$C1,1)=LARGE($A1:$C1,2),"Equal ("&CHAR(64+MATCH(LARGE($A1:$C1,1),$A1:$C1,0))&CHAR (64+MATCH(LARGE($A1:$C1,1) ,$A1:$C1,1))&")",IF(LARGE($A1:$C1,2)=LARGE($A1:$C1 ,3),"Equal ("&CHAR(64+MATCH(LARGE($A1:$C1,2),$A1:$C1,0))&CHAR (64+MATCH(LARGE($A1:$C1,3) ,$A1:$C1,1))&")",CHAR(64+MATCH(LARGE($A1:$C1,2),$A 1:$C1,0))))) In Cell F1, use: =IF(LARGE($A1:$C1,1)=LARGE($A1:$C1,3),"Equal (ABC)",IF(LARGE($A1:$C1,2)=LARGE($A1:$C1,3),"Equal ("&CHAR(64+MATCH(LARGE($A1:$C1,2),$A1:$C1,0))&CHAR (64+MATCH(LARGE($A1:$C1,3) ,$A1:$C1,1))&")",CHAR(64+MATCH(LARGE($A1:$C1,3),$A 1:$C1,0)))) Copy down as far as needed. Cheers -- macropod [MVP - Microsoft Word] "gkb" wrote in message ... Hi Can someone help me to find out and place the highest, second highest, third highest and so on in group of numbers in a sheet? For example: QUESTION: A B C D E F 1004 1004 1004 1518 1517 797 1332 1320 1500 1337 1337 1004 1337 1337 1000 1940 560 1560 I need to find out which is highest, second highest and third highest from A,B & C and the D, E & F columns should display Highest, Second Highest, Third Highest accordingly. Where the numbers are equal, i need a display Equal(AB) or Equal(BC) or Equal (ABC) as the case depends. I might be overthinking on this, but I am just not getting the right formula. If someone could please help on this... please GKB |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Display the Highest, Second Highest, Third Highest and so on..
Hi "bj"
Thanks a ton. I am still trying. Hopes it works. gkb "bj" wrote: try =LARGE($A1:$C1,column()-3) "gkb" wrote: Hi, Thank you. I did try this formula... and got the desired result. But I am not able to paste this formual in all cells. There is some "array" formula working here... =LARGE($A$1:$C$1,1) Hope you will get me a solution to this also. Thank you once again gkb "Gary''s Student" wrote: Use the LARGE() function. -- Gary's Student "gkb" wrote: Hi Can someone help me to find out and place the highest, second highest, third highest and so on in group of numbers in a sheet? For example: QUESTION: A B C D E F 1004 1004 1004 1518 1517 797 1332 1320 1500 1337 1337 1004 1337 1337 1000 1940 560 1560 I need to find out which is highest, second highest and third highest from A,B & C and the D, E & F columns should display Highest, Second Highest, Third Highest accordingly. Where the numbers are equal, i need a display Equal(AB) or Equal(BC) or Equal (ABC) as the case depends. I might be overthinking on this, but I am just not getting the right formula. If someone could please help on this... please GKB |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| 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 | |||
| Help pls! Max func to display value of different cell? | Excel Worksheet Functions | |||
Linear Mode
