Sum multiple cells to find top three and return a Text

R

Rory32

New member
Joined
Jun 17, 2015
Messages
4
Reaction score
0
Points
0
Hi All,

I am trying to build a formula to find the top 3 largest names by exposure without using a pivot table - i can't get it to work and have been stewing over this all afternoon. any ideas?


Name Exposure
aaaaa0.0518%
bbbbb0.0383%
ccccc0.1785%
ddddd0.2850%
fffffff0.8151%
gggggg-0.3731%
aaaaaa0.9892%
bbbbbb0.0051%
ccccccc2.7778%
dddddd-0.0140%
eeeeee3.7129%
fffffff1.3034%
gggggg0.3421%
aaaaaa-0.0155%
fffffff0.0679%
hhhhhh0.3542%
xxxxxx2.3560%
yyyyyy0.4240%
pppppp-0.0814%
iiiiii0.0364%


Thanks
Rory
 
Hi,

I think it's better ff you can share your example into an Excel file.

However, you can try this, assuming that these data are in columns A and B :

1st largest name =INDEX(A1:A20,MATCH(LARGE(B1:B20,1);B1:B20,0))

Good luck
 
hossat said:
Hi,

I think it's better ff you can share your example into an Excel file.

However, you can try this, assuming that these data are in columns A and B :

1st largest name =INDEX(A1:A20,MATCH(LARGE(B1:B20,1);B1:B20,0))

Good luck
Click to expand...


Hi Hossat, thanks for the below but i already tried this formula, yes you're right it will return the largest single name but it won't sum cells with the same name and return the highest - any other suggestions?

Thanks
 
You would need to add a helper column first....

e.g.

in C2:

=SUMIF($A$2:$A$21,A2,$B$2:$B$21)

copied down.

Then use the formula Hossat gave you, replacing the B1:B20 range

e.g

=INDEX($A$2:$A$21,MATCH(LARGE($C$2:$C$21,ROWS($D$1:$D1),$C$2:$C$21,0))

copied down as far as needed to get top n names.
 
the helper column is useful but your formula above is returning "you have entered too many arguments for this function"
Any other suggestions?

Thanks
 
Sorry... lost a closing parenthesis in there..

try:

=INDEX($A$2:$A$21,MATCH(LARGE($C$2:$C$21,ROWS($D$1:$D1)),$C$2:$C$21,0))
 
NBVC said:
Sorry... lost a closing parenthesis in there..

try:

=INDEX($A$2:$A$21,MATCH(LARGE($C$2:$C$21,ROWS($D$1:$D1)),$C$2:$C$21,0))
Click to expand...


This works , thanks very much
Apologies I thought I got back on Friday
 
You must log in or register to reply here.
Back
Top