[plug] Open office calc

[David Dartnall]

On Sun, 26 Feb 2006 08:52 pm, Alex Nordstrom wrote:
> Sunday, 26 February 2006 19:33, Harry McNally wrote:
> > David Dartnall wrote:
> > > I need to get the sum of the 7 (say) highest numbers in each row.
> > > Sounds simple but all I've found are the basic functions which
> > > don't seem to fit the requirements.
> >
> > There is quite a list if you start OOo Help, choose the index and
> > select "functions->in Calc". Look through those. Choosing an
> > algorithm using those functions would be handy if you could use a
> > sort (didn't see one) or copy the row into a scratch row, find the
> > index of the highest value (using max), sum that and then zero the
> > indexed element and call max again .. repeat 7 times. It's manky but
> > uses only max.
>
> LARGE (which returns the kth largest value in a set) seems like a better
> fit (no need to zero the highest value).
>
> So something like:
>
> = SUM( LARGE( RC:RCn; 1 ); LARGE( RC:RCn; 2 ); ...; LARGE( RC:RCn; k - 1 );
> LARGE( RC:RCn; k ) )
>
> Where R is the row of the data set; C is the first column of the data
> set and Cn is the last; and k is the number of values to sum.
>
> There is probably a more elegant way.

Thanks Alex, there may be but this works and will save me a lot of time. The 
only problem is that it doesn't like rows that contain less than the (7) say 
nominated cells being tested (rows are all the same length but some cells 
empty in this application). It returns #VALUE! which leaves the thing untidy. 
I can clean that up by hand tho'

As far as Harry's suggestion of writing a macro is concerned, thats going up 
another level - but something for me to follow up. Like that word manky!

regards and thanks again
Dave D