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