Snewsh wrote: ↑Wed May 22, 2024 2:18 pm
I need to calculate the best 7 from 12 weights from each individual angler.
In general, you use the LARGE() function to determine the largest 7 values so you can sum them,
=LARGE(range;α) where α varies from 1 to 7. This is shown in the attachment, sheet Data, columns Q through X.
The data layout you have chosen for
Best_Weights.ods might be nice for presentation, but it will make calculations very difficult for you, as exhibited by your long formula in B9:
=INT(SUM(H9+J9+M9+P9+S9+V9+Y9+AB9+AE9+AH9+AK9+AN9)+SUM(I9+K9+N9+Q9+T9+W9+Z9+AC9+AF9+AI9+AL9+AO9)/16)
With your layout you will find it nearly impossible to do simple tasks like summing the 7 largest values because you don't have the values arranged as a range for LARGE(). If it were my spreadsheet I would have one sheet for the data, arranged so calculations are simple, then a second sheet which copies cells from the data sheet and presents them in the layout that you want. For summing and determining the largest values, data is stored in ounces in one cell, because you need that for LARGE(). There will be no convenient method to use LARGE() if pounds and ounces are in separate cells. The cell value is later converted to pounds and ounces for display. I will guess that the data is presented to you in pounds and ounces, so enter it as a formula. For example 19 pounds 10 ounces is entered into B3 as
=19*16+10, which shows as cell value 314.
SUM(H9+J9+M9+…) is redundant. The plus operators add the 12 cell values together and pass the total to SUM. The SUM function then receives only a single value and the result of the SUM function is the same as the single value it was passed. Use either:
•
(H9+J9+M9+…) let + do the addition or
•
SUM(H9;J9;M9;…) let SUM do the addition
This is explained in more detail in
[Tutorial] Ten concepts that every Calc user should know.
I saw in your spreadsheet that
1st match doesn't count. But the values from this first match seem to be included in your formulas for columns B and C. I didn't know what the phrase meant so I ignored it.
If this solved your problem please go to your first post use the Edit ☐ button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.7.8, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
