libreoffice calc - Looking for math function for finding highest possibile sum

Question

I have a set of 4-digit numbers from which I have to find the largest possible sum consisting of a maximum of 5 items not exceeding 24 thousand but not less than 21 thousand. Even though it sounds trivial and I do it by hand without any problems (the collections are not very numerous) I can't get it over my head to transfer it to a spreadsheet.

@edit Ex. Set of data {4785,4890,5030,5790,6020,3230,4500,5000,4550,2300,5400,5350.....}

question from:https://stackoverflow.com/questions/65887738/looking-for-math-function-for-finding-highest-possibile-sum

Answer 1

Tools -> Solver works for the values in your example. Here is the spreadsheet I used, with data in cells A2 through A13.

   Data    Chosen Rows Chosen Values   Calculation
   4785    5           5790            24000
   4890    7           3230
   5030    4           5030
   5790    12          5400
   6020    10          4550
   3230                                      
   4500                                      
   5000                                      
   4550                                      
   2300                                      
   5400                                      
   5350                                      
       

The formula for C2 is =INDIRECT("A"&B2), filled down to C6. Then D2 is simply =SUM(C2:C6).

Here are the Solver settings.

Also under Options, use the non-linear solver. Specify that variables are integer (this was required even though I added integer as a limiting condition) and non-negative.

EDIT:

every value can be used only once

In that case, enter the following as an array formula in cell B8, explained at https://stackoverflow.com/a/38286032/5100564.

=SUM(1/COUNTIF(B2:B6;B2:B6))

Then add another limiting condition to the solver: $B$8 => 5.