Affect Two Cells Using A Single Formula

[Sutniko]

I am trying to create a formula to help my scheduler and materials manager order enough material to meet demands that are greater than what we can produce in our current lead time. I want to start as far out as my customers orders are planned and roll overflow orders backwards until all quantities are below the specified max production. Here is what I am working with as well as a formula that would do the trick if it were actually legit....

Max Production: $A$1

Monthly Order Quantities: A2:S2

If any given month's production is greater than $A$1 then I need the overage rolled into the previous month and so an and so forth.

Logic:

S2= if (S2<=$A$1,S2, if (S2>$A$1, R2=R2+(S2-$A$1) and S2=$A$1))

R2= if (R2<=$A$1,R2, if (R2>$A$1, Q2=Q2+(R2-$A$1) and R2=$A$1))

Q2= if (Q2<=$A$1,Q2, if (Q2>$A$1, P2=P2+(Q2-$A$1) and Q2=$A$1))

[Richard Buttrey]

Hi,

Try in A3 copied across

=MIN($A$1,SUM(A2:$T2)-SUM(B3:$T3))

[Sutniko]

Awesome! It works. I am still working through the logic in your formula so I can understand exactly what is happening there but... problem solved.

Thanks!

[Richard Buttrey]

That's great. Thanks for the feedback.

I originally thought that it would need a macro since at first glance it seemed that there was a recursive element, but after a little more thought and playing around a bit the formula you see appeared.