Note: The input file is an excel file. However, I am only looking for help with the algorithm as I can then code it in VBA.
I need to scan both columns (shown below) to find any number of column 1 values that when summed match the setpoint (E2) and column 2 values that when averaged match the setpoint (E3). One constraint is that the chosen values in column 1 and column 2 must be from the same row (or same 'index', shown in the image highlighted in blue and green).
Explanation of the solution below: What it should do is say 'A2 and A6 sum to 7 (match cell E2), and the corresponding B2 and B6 average to 3' (match cell E3). Therefore, these rows should be selected/highlighted in any appropriate way. (note that technically the first solution would be rows 2 and 3 as they fit the sum/average setpoints also).
Example that does not work as a solution: It can't be A4 and A5 because although they add to 7, B4 and B5 average to 1.5, not the setpoint of 3.
Image of the problem (with a solution highlighted):
A solution for one of the cases (below) works only for the case where there are two rows that will sum/average to the setpoints. It will not work for cases where more than two rows are required. The solution should work for any number of rows required.
Reason for this solution: B6:B8 sums to the setpoint of 8 and C6:C8 average to the setpoint of 3
I am looking for help with an algorithm. If the solution is a large number of rows required (>100), how can I iterate through the combinations efficienctly to find a set of rows that both sums/averages to the setpoints?
It may not be possible to find a solution to both setpoints, in this case finding a solution within an error is also ok
Note: If there is a more suitable stack exchange please advise.
Finding an algorithm is NP-Hard, therefore I am also looking for appropriate simplifications to avoid this. An error term may be an example of one simplification.