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

BeaconRuth

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

richk

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

SteppenHerring

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

SteppenHerring wrote:

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:

gbs

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.

BeaconRuth

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