[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
hotmetal
Newbie

Joined: 14 Mar 2005
Posts: 4
Location: U.K.

 Posted: Mon Mar 14, 2005 6:39 am    Post subject: Summing largest 10 figures in a colunm 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! Alan
bobharvey
Super User

Joined: 23 Apr 2004
Posts: 1075
Location: Lincolnshire

 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

 Posted: Mon Mar 14, 2005 7:04 am    Post subject: 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 ,_________________James www.8daysaweek.co.uk - A User-Focused OOo site
hotmetal
Newbie

Joined: 14 Mar 2005
Posts: 4
Location: U.K.

 Posted: Mon Mar 14, 2005 7:22 am    Post subject: 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

Joined: 16 Jun 2004
Posts: 418
Location: Mesa, AZ

 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
Super User

Joined: 29 May 2004
Posts: 2477
Location: 3rd Rock From The Sun

 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.

 Posted: Mon Mar 14, 2005 9:04 am    Post subject: 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 Alan
richhill

Joined: 16 Jun 2004
Posts: 418
Location: Mesa, AZ

 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.

 Posted: Mon Mar 14, 2005 9:45 am    Post subject: 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
Super User

Joined: 23 Apr 2004
Posts: 1075
Location: Lincolnshire

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

 SlideRule wrote: =SUMIF( \$A\$1:\$A\$100 ; ">=" & LARGE(\$A\$1:\$A\$100; 10) )
That's clever
richhill

Joined: 16 Jun 2004
Posts: 418
Location: Mesa, AZ

 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
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum