Need to choose maximum values to selected sum

Solver seems like it should be able to do this. Set up is similar to a "subset sum" type of problem https://superuser.com/questions/2049...-a-given-total Set up the spreadsheet to calculate total power and the difference (absolute value or squared difference might be best) between total power and your target value. Then tell Solver to set this to a minimum by changing the inverters used subject to the constraint that you only use 2 inverters.

It's probably not necessary for us to know because the overall strategy should work either way, but you say that you need the sum of inverters to be closest to 75.1 kW, then state that the solution you want is the 50+20=70. I notice that 40+36=76 is closer to 75.1 than 70 is. I probably just do not understand exactly what you mean by "closest to" in the context of the problem (perhaps it really means "closest to, but never greater than"?). It probably doesn't matter that I understand it, but just be sure that you are clear exactly what you mean by "closest to" as you set up the problem so that you get the solution you want.

Solver is not good solutions in this situation I need to make reference to the cell, and with solver I can only write set value.

I'm not sure I understand this concern. What about Solver forces you to write only a set value?

I think the solver is very suitable for your needs. You only need to define what you want in a smart way.

This quote is not smart:

It would be not good if excel suggest to chose lot of smaller values

Smart is for example: "In the solution there are max 2 values below 20".

And so on.

In the solver you should refer to values in cells. You only had to tell the tell the solver what you want to achieve and add constraints that apply to the problem

I mean that in Solver you need to write manually value. I would like to make something that when value in cell changes, automatically changes inverter quantity. As with Solver every time power value changes you need to write manually Value of.

Again, I'm not sure I understand this, though it could be because I am not sure exactly how you are setting up you Solver/spreadsheet model. I can see where some variations would require you to manually enter a new value in the "set target cell to a value" field of the Solver dialog, there are almost certainly other ways to set this up so you don't need to manually edit that field each time. For example, when I set up a Solver/spreadsheet model to find a target value, I don't enter that target value in the "to Value" field. I enter that value in a spreadsheet cell, add a simple =formula-target value cell to the spreadsheet, then tell Solver to set that to a value of 0. Any time I need to change the target value, I need only edit the target value cell in the spreadsheet. I need not make any manual edits to Solver.

Can you explain how you are setting up your Solver/spreadsheet model that is requiring these undesirable manual edits, and explain changes you are allowed to make? As HansDuowe says, we can usually minimize edits/interactions with Solver/spreadsheet if we are a little smart and clever in designing the Solver/spreadsheet model.

For others' benefit, link sent to me: https://www.extendoffice.com/documen...given-sum.html

Your tutorial shows them wanting to find values that add up to 480, so they enter 480 into the "value of" field in Solver. This is where I would enter 480 into a cell in the spreadsheet (maybe B11 in their example), add another cell that computes the difference (maybe B12 =B11-B10), then tell Solver to find a value of 0. When I want to find a different target value, I enter my new target value into B11 and rerun Solver without further modification.

For something like yours, where your target is "max value no larger than target," I would set up something similar (B10 is SUMPRODUCT() function, B11 is target value, B12 is B11-B10), then tell Solver to set B12 to a minimum (I think I have my signs right, if not, maximum), by changing decision variables, subject to the constraint that B12 is greater than or equal to 0 (less than or equal to 0 if my signs are backwards). Whenever my target value changes, I need only change the value in B11 and run Solver.

In the example of MtShorty you should also tell the solver that B12 should stay positive (or you need to minimize ABS(B11-B10)).

The usual constraint to add for that is to have a some kind of "counting" function in the spreadsheet that counts how many numbers are used. In extended office's sample, this might be =SUM(B2:B9) then have a constraint in Solver that this cell must be less than or equal to a threshold (2 or 3 or whatever suits your fancy).