Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Add largest Values
I have a list something like this
A C 2 22 3 25 4 36 11 38 9 45 4 36 5 22 2 45 5 25 I would like to Sum the values in column A according to the values in column C Add the 1st largest of column C 45=11 2nd largest 38=11 3rd largest 36=8 ect when I use the =large() formula and there is more than one number, well it doesn't work, there are 2 -45's so the 3rd largest number is 38 when it should be the 2nd largest Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Add largest Values
look at the rank function,there is a section there on how to rank ties.Hope
you can use it! -- paul remove nospam for email addy! "damorrison" wrote: I have a list something like this A C 2 22 3 25 4 36 11 38 9 45 4 36 5 22 2 45 5 25 I would like to Sum the values in column A according to the values in column C Add the 1st largest of column C 45=11 2nd largest 38=11 3rd largest 36=8 ect when I use the =large() formula and there is more than one number, well it doesn't work, there are 2 -45's so the 3rd largest number is 38 when it should be the 2nd largest Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Add largest Values
you will need an additional 3 columns for this to work
Assuming row 1 is a heading row In column D insert this formula =IF(COUNTIF($C$2:C2,C2)=1,RANK(C2,$C$2:$C$10,1),"" ) =IF(COUNTIF($C$2:C3,C3)=1,RANK(C3,$C$2:$C$10,1),"" ) =IF(COUNTIF($C$2:C4,C4)=1,RANK(C4,$C$2:$C$10,1),"" ) =IF(COUNTIF($C$2:C5,C5)=1,RANK(C5,$C$2:$C$10,1),"" ) =IF(COUNTIF($C$2:C6,C6)=1,RANK(C6,$C$2:$C$10,1),"" ) =IF(COUNTIF($C$2:C7,C7)=1,RANK(C7,$C$2:$C$10,1),"" ) =IF(COUNTIF($C$2:C8,C8)=1,RANK(C8,$C$2:$C$10,1),"" ) =IF(COUNTIF($C$2:C9,C9)=1,RANK(C9,$C$2:$C$10,1),"" ) =IF(COUNTIF($C$2:C10,C10)=1,RANK(C10,$C$2:$C$10,1) ,"") In column E insert this formula =IF(D2<"",OFFSET($C$2,MATCH(LARGE($D$2:$D$10,COUN T($D$2:D2)),$D$2:$D$10,0)-1,0),"") =IF(D3<"",OFFSET($C$2,MATCH(LARGE($D$2:$D$10,COUN T($D$2:D3)),$D$2:$D$10,0)-1,0),"") =IF(D4<"",OFFSET($C$2,MATCH(LARGE($D$2:$D$10,COUN T($D$2:D4)),$D$2:$D$10,0)-1,0),"") =IF(D5<"",OFFSET($C$2,MATCH(LARGE($D$2:$D$10,COUN T($D$2:D5)),$D$2:$D$10,0)-1,0),"") =IF(D6<"",OFFSET($C$2,MATCH(LARGE($D$2:$D$10,COUN T($D$2:D6)),$D$2:$D$10,0)-1,0),"") =IF(D7<"",OFFSET($C$2,MATCH(LARGE($D$2:$D$10,COUN T($D$2:D7)),$D$2:$D$10,0)-1,0),"") =IF(D8<"",OFFSET($C$2,MATCH(LARGE($D$2:$D$10,COUN T($D$2:D8)),$D$2:$D$10,0)-1,0),"") =IF(D9<"",OFFSET($C$2,MATCH(LARGE($D$2:$D$10,COUN T($D$2:D9)),$D$2:$D$10,0)-1,0),"") =IF(D10<"",OFFSET($C$2,MATCH(LARGE($D$2:$D$10,COU NT($D$2:D10)),$D$2:$D$10,0)-1,0),"") In column F insert this formula =IF(D2<"",SUMIF($C$2:$C$10,E2,$A$2:$A$10),"") =IF(D3<"",SUMIF($C$2:$C$10,E3,$A$2:$A$10),"") =IF(D4<"",SUMIF($C$2:$C$10,E4,$A$2:$A$10),"") =IF(D5<"",SUMIF($C$2:$C$10,E5,$A$2:$A$10),"") =IF(D6<"",SUMIF($C$2:$C$10,E6,$A$2:$A$10),"") =IF(D7<"",SUMIF($C$2:$C$10,E7,$A$2:$A$10),"") =IF(D8<"",SUMIF($C$2:$C$10,E8,$A$2:$A$10),"") =IF(D9<"",SUMIF($C$2:$C$10,E9,$A$2:$A$10),"") =IF(D10<"",SUMIF($C$2:$C$10,E10,$A$2:$A$10),"") Would be nice to be able to nest all the formulas but I don't think it is possible |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to find the largest change of values in a column | Excel Worksheet Functions | |||
How do I generate a list from a range of values | Excel Worksheet Functions | |||
find largest values, then return corresponding row values. | Excel Discussion (Misc queries) | |||
How do I return the cell address of the largest of a set of values | Excel Discussion (Misc queries) | |||
Ranking cells largest to smallest | Excel Discussion (Misc queries) |