Microsoft Excel

[Marko77]

Hi,

Any Excel guru's lend me a quick formula?

Say I have a column of 60 numbers but I want to put in a formula at the bottom to total the 30 largest numbers within that 60 - how could I do this.

Much obliged to anyone who can advise. I'm sure I've seen the formula before but I can't remember how it goes.


Cheers

 

[fundy]

=SUM(IF(COUNT(A1:A60)<30,A1:A60,LARGE(A1:A60,{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30})))


that should do the trick though may be shorter ways to do it (or do via a pivot table)

 

[Marko77]

Thank you very much!!

 

[chrisd]

=SUM(IF(COUNT(A1:A60)<30,A1:A60,LARGE(A1:A60,{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30})))


that should do the trick though may be shorter ways to do it (or do via a pivot table)

Couldn't have put it better myself ..... in fact I couldn't have said myself! Always nice to find a boffin when you need one



Chris

 

[Leftie]

Far too complicated for an old f*rt like me.

I would just have sorted the list - highest to lowest and then just do a sum of rows 1 - 30.

Heck, what do I know? It's 5 years since I used a spreadsheet.

 

[bobmac]

Far too complicated for an old f*rt like me.

Heck, what do I know? It's 5 years since I used a spreadsheet.

What's a spreadsheet?

 

[alan32195]

Hi Marko,

A bit late maybe but this will work just as well:

=SUMPRODUCT((A1:A60>=LARGE(A1:A60,30))*(A1:A60))

 

[Marko77]

Cheers also Alan, will keep a note of that in my formula's list.

Leftie - I was so focused on trying to figure out the formula simple logic defied me - Good logical thinking there, I may have got there had there not been such a quick response here...