So I am trying to automate a problem, say if you have up to 5 numbers and there is at least one negative number, how can this negative number be minimised by taking from the positive numbers?

Take for instance the array (-27.5, -22, 19.5) by looking at this it’s quite easy, we can distribute the 19.5 as 19.5 x 27.5 / (27.5 + 22) = 10.8 and 19.5 x 22 / (27.5 + 22) = 8.7, so we get (-27.5 + 10.8, -22 + 8.7, 19.5 – 19.5) = (-16.7, -13.3, 0).

Take another example (14, -6, 18) should give -2.625, 6, -3.375 to then give (11.375, 0, 14,625).

This is quite straight forward maths as I can identify which cells I need to distribute but am finding it really hard in excel to program.

So say if the row is C14:E19, the C14 cell would be the following:

=IF(C14<0, MIN(SUMIF($C14:$E14,”>0″), ABS(C14)) * ABS(C14) / SUMIF($C14:$E14,”<0″), IF(C14>0, -C14 * SUMIF($C14:$E14,”<0″) / SUMIF($C14:$E14,”>0″), 0))

This firstly gives me the incorrect signs in my test cases but that’s a minor thing. The other problem is, take my first case, on the 19.5 it returns 49.5 rather than -19.5 to equalise it to zero and on the second problem -6 returns another -6.

All in all I feel like I’m nearly there but having spent a decent amount of time on this I can’t get any further. I’m hoping someone with a fresh head can stop me being stupid….