Solver Feasibility troubleshooting

bracurrie  wrote:  ``I am very interested in pursuing: "But we can achieve the same relative distribution for the positive increases in G4:G20."``

The following image demonstrates what I have in mind.  See the attached Excel file for details.

However, first, I should say that I am no longer confident that this is a reliable method.  I wonder if there are cases where the "w new" amounts (I4:I20) might overshoot the target allocations (column D) for the positive increases in column G.  But I'm afraid I do not have time to pursue this further.

I explain this as a multi-step procedure.  I believe it make things clearer.  But especially with Excel 365, I suspect that we can consolidate some steps and formulas.

(Since I do not use Excel 365, any assistance with that consolidation must come from someone else.)

1. In G4:G20 ("trial new units"), we determine how to allocate the target total new units (E23) so that the distribution of the target total "w new" (E24) in I4:I20 would achieve the target "average scale" (D4:D20), allowing for negative values.

The formula in G4 is:  =ROUND($E$24*D4 - E4, 0)

2. In E25, we count the number of negative values in G4:G20.

The formula is:  =COUNTIF(G4:G20,"<0")

If E25 is zero, we can simply "copy" the values in G4:G20 into H4:H20.  Skip to step #5.

3. But if E25 is non-zero, we must avoid increasing the corresponding units on hand in I4:I20.

To that end, in E26, we calculate the sum of the target "average scale" percentages that correspond to the positive values in G4:G20.

The formula is:  =SUMIFS(D4:D20,G4:G20,">=0")

4. Then in H4:H20, we prorate the target new units (E24) by the target "average scale" percentages that correspond to the positive values in G4:G20 relative to their sum in E26.

Effectively, the formula in H4 would be:  IF(G4<0, 0, ROUND($E$23*D4 / $E$26, 0))

5. Consolidating the formula in step #2 for E25=0 and the formula in step #4, the actual formula in I4 is:

=IF($E$25=0, G4, IF(G4<0, 0, ROUND($E$23*D4 / $E$26, 0)))

6. The "w new" formula in I4:I20 is:  =E4+H4

7. Finally, in J4:J20, we calculate the new distribution.

The formula in J4 is:  =I4/$I$21

If E25=0 (all positive increases in G4:G20), the percentages in J4:J20 should be the same as the target "average scale" in D4:D20, subject to integer rounding.

Otherwise if E25>0 (some negative changes in G4:G20), at the very least, the percentages in J4:J20 should be a step in that direction.

-----

A word about rounding....

I have rounded each calculation to integers, as you requested.

But note that the sum of the "trial new units" (G21) is 3499, not 3500.

And it is merely a coincidence that the sums in H21 and I21 are their intended targets.

Such rounding "error" should be expected because "the sum of the rounded parts is not necessarily the same as the rounded whole".

For that reason, I usually do not to round intermediate calculations.

Instead, when all is said and done, I will add one or more columns (as needed) to round the non-integer values.  That allows us to implement a "clever" rounding algorithm that distributes the rounding "error" and results in exactly the target total.