Ask Your Question
0

Calc - Sum of top values in a row [Closed]

asked 2016-02-16 18:26:25 +0200

irs gravatar image

updated 2016-02-28 14:16:13 +0200

The answer question " How to select the top "values" in each row of the spreadsheet [closed]" explained how to identify to top n items in a row.

I want to SUM the top 15 values in a row containing up to 30 values I can do this in two ways (both long winded) [values held in $B2:$AR2]

  1. create 15 new columns and populate them with "=LARGE($B2:$AR2,n)" where n = 1-15 and then sum the 15 columns
  2. create a single total column with a formula of 15 expressions =(LARGE($B2:$AR2,1)+LARGE($B2:$AR2,2)+LARGE($B2:$AR2,3)+ .....LARGE($B2:$AR2,15)) this gets very unwieldy for the top 50 !

is there a better way of doing this in Calc

In Excel I can use the formula =SUM(LARGE($B2:$AR2,{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15})) in calc this just returns the highest value.

(Editing: I ('Lupp') only added the link to the post referred to at the beginning of this question.)

edit retag flag offensive close merge delete

Comments

How about the previously to sort the row and simplify sum the first (or last) 15 values?

JohnSUN gravatar imageJohnSUN ( 2016-02-17 04:53:27 +0200 )edit

I probably didn't describe the problem well enough. The spreadsheet contains the weekly scores of up to 20 or 30 players in a golf competition running over 30 weeks. The best 15 scores (out of a possible 30) count. Each week the scores are entered into the sheet and I would like the calculation to be automatically carried out. The sort & sum method will work ok but requires multiple operations. The Excel solution has a single cell for each row calculating the value we want.

irs gravatar imageirs ( 2016-02-17 10:45:15 +0200 )edit

Well, then use the formula pierre-yves samyn - it succinctly and elegantly. Just change the range A1:A15 on absolute reference A$1:A$15 - this will avoid possible errors when the formula moving from cell to cell

JohnSUN gravatar imageJohnSUN ( 2016-02-17 12:11:04 +0200 )edit

1 Answer

Sort by » oldest newest most voted
1

answered 2016-02-17 09:22:50 +0200

pierre-yves samyn gravatar image

Hi

Try something like:

=SUMPRODUCT(LARGE($B2:$AR2;ROW(A1:A15)))

Regards

edit flag offensive delete link more

Comments

Thanks (and to JohnSUN) - gives me exactly what I want. I'm now trying to work out how it works - good for the brain !

irs gravatar imageirs ( 2016-02-17 13:36:16 +0200 )edit

Thank's for the feedback... and very sorry for the lack of explanation.

ROW(A$1:A$15) returns an array containing the numbers 1 to 15. @JohnSUN is absolutely right to recommend the passage to absolute references.

SUMPRODUCT works with arrays, so no need to validate by Ctrl+Shift+Enter

Regards

pierre-yves samyn gravatar imagepierre-yves samyn ( 2016-02-17 13:59:57 +0200 )edit
Login/Signup to Answer

Question Tools

2 followers

Stats

Asked: 2016-02-16 18:26:25 +0200

Seen: 1,218 times

Last updated: Feb 28 '16