Anyone really good with formulae in Excel? Needing help.....

BeaconRuth
BeaconRuth Posts: 2,086
edited August 2009 in The bottom bracket
I dunno if this is a long shot but here goes:

I've got a row of 20 cells, some of which, randomly distributed, contain a numerical value. The number of cells containing a value is unknown. I want a formula which will find the sum of the highest 5 values in that row (or the sum of all of the values in the row if the number of populated cells is 5 or fewer). Oh yes, and sometimes the same value is repeated in different cells, so if the 5th and 6th highest numbers are both the same value, then one will need to be included in the sum but the other excluded.

Is there an easy way of doing this? My programming skills aren't up to much. :( I can't even work out a long-winded way of doing it, let alone a slick elegant way.

Thanks!
Ruth

Comments

  • richk
    richk Posts: 564
    Ruth

    You could probably use the LARGE function to do that.

    eg = LARGE(A:A,1) + LARGE(A:A,2)... + LARGE(A:A,5)

    or for a row =large(10:10,1) etc

    Richard
    There is no secret ingredient...
  • Take a look at COUNTIF and SUMIF functions. You might be able to do something with one of those. The LARGE function thing might help as well. Even better, consider not using Excel for anything proper.
  • hopper1
    hopper1 Posts: 4,389
    Take a look at COUNTIF and SUMIF functions. You might be able to do something with one of those. The LARGE function thing might help as well. Even better, consider not using Excel for anything proper.

    +1

    Though I find database software a real nightmare :oops:
    Start with a budget, finish with a mortgage!
  • gbs
    gbs Posts: 450
    Not an expert, but Excel 2007 has a Data tab and within that a Sort function. Sort yr row by size ( descending) and apply the sigma function to the first 5 cells.

    Hope this does the job for you.
    vintage newbie, spinning away
  • BeaconRuth
    BeaconRuth Posts: 2,086
    RichK - ACE! That's it.......... just the job! Absolutely brill - these things are soooo easy when you have the right function! I spent ages yesterday getting nowhere with SUMIF and COUNTIF but there was no easy way to pick out the 2nd highest, 3rd highest numbers etc when I didn't know how many numbers I was working with.

    Thanks all for your help.

    Ruth