Summing largest 10 figures in a colunm

hotmetal · Newbie Joined: 14 Mar 2005 Posts: 4 Location: U.K.

Hi All,

I'm new to OpenOffice, I've always used Excel, so now I'm trying to use some of my Excel formulae in OOCalc. I just can't get the one below to work?

=SUM(LARGE($A$1:$A$100,{1,2,3,4,5,6,7,8,9,10}))

It is used to sum the largest 10 (or more if you ask) figures in a colunm.

If anyone could help I'd be very happy! Laughing

Alan

bobharvey · Posted: Mon Mar 14, 2005 7:00 am Post subject:

you do know to use semicolons in parameter lists instead of commas, don't you?

have a look in the help for LARGE

8daysaweek.co.uk · Super User Joined: 29 Nov 2003 Posts: 2130 Location: UK

Alan,

I think LARGE() will only accept one rank parameter.

=LARGE($A$1:$A$100;1) works to find largest value
=LARGE($A$1:$A$100;2) works to find second largest value, but I can't repeat the 2. To sum them I would have to nest the above expressions in a SUM() formula.

BFN Smile

,
_________________
James
www.8daysaweek.co.uk - A User-Focused OOo site

hotmetal · Newbie Joined: 14 Mar 2005 Posts: 4 Location: U.K.

Hi Bob and James,

Thanks for getting back so fast,

Bob, yes I'd worked out the different separators and I have checked out the help file for LARGE, it seem that James has found the issue, OOCalc will only accept single rank values.

James, thanks, I've nested the expresions and it works ok for me, just a bit clumsey.

If anyone knows how to make multi rankings that would be good to know.

Thanks Again

Alan

richhill · Posted: Mon Mar 14, 2005 8:16 am Post subject:

Very much a related topic...

http://www.richhillsoftware.com/blog/archives/2005/03/largest_values.html

The following array function should work - although I'm having probs on Solaris 1.1.x...
{=SUM(LARGE($A$1:$A$100;row(b1:b10)))}

SImilar array function {=sum(row(b1:b10))} works

as does {=row(b1:b10)}

I'll see if it works on Windows XP 2.0beta later....

Sliderule · Posted: Mon Mar 14, 2005 8:35 am Post subject:

Alan:

Perhaps, from your original question . . . you could use this function.

=SUMIF( $A$1:$A$100 ; ">=" & LARGE($A$1:$A$100; 10) )

The idea, by combining the SUMIF function, with a 'condition' where the value is greater then, or equal to the tenth largest . . . it might work.

Hope this helps.

SlideRule

hotmetal · Newbie Joined: 14 Mar 2005 Posts: 4 Location: U.K.

Thanks Rich & Sliderule,

I've tried your expression Rich ( cut and Paste,) but no joy. Sliderule I've put yours in and it's just right.

Thank you both Very Happy

Alan

richhill · Posted: Mon Mar 14, 2005 9:36 am Post subject:

I should point out that the array functions should not be cut/pasted from my prior postings .

They should be entered without the enclosing curly braces and applied with Shift-Ctrl-Enter

Apologies if I'm stating the obvious - just needed to make sure.
_________________
OOo Calc tips: http://www.openofficetips.com

hotmetal · Newbie Joined: 14 Mar 2005 Posts: 4 Location: U.K.

Thank Rich,

I'm quite new to this, I'm a photographer really, so that was very helpful, thanks, I'll try again.

Alan

bobharvey · Posted: Mon Mar 14, 2005 10:00 am Post subject:

richhill · Posted: Mon Mar 14, 2005 10:30 am Post subject:

{=SUM(LARGE($A$1:$A$100;row(b1:b10)))} does indeed work on Win XP OOo 2.0Beta.

A lot of the Excel array functions use the integer array construct {1,2,3,4,5,6} inside
the array formulas.

This won't work in OOo Calc - but it looks like swapping in a ROW function does the trick. More exploration/research called for - I'll take a look at the common Excel array formulae and see how portable they are.
_________________
OOo Calc tips: http://www.openofficetips.com