Search code examples
libreoffice-calc

Looking for math function for finding highest possibile sum


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.....}


Solution

  • 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.

    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.