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

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

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