[plug] Open office calc

[Alex Nordstrom]

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.

-- 
Alex Nordstrom
http://lx.n3.net/
Please do not CC me in followups; I am subscribed to plug.
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 189 bytes
Desc: not available
URL: <http://lists.plug.org.au/pipermail/plug/attachments/20060226/448885bc/attachment.pgp>