This follows on from a previous post.
Attached is the simplest presentation showing the issue.
Row five shows the stock and order process over weeks 28 - 35.
A9 - D16 extract the relevant data, and E9 - E16 should calculate the Lead Times on the basis of finding when (or whether) any "Outstanding orders" at the end of a particular week will be satisfied by future deliveries into the operation (because the Unfilled Orders are at the end of the week in question, so C9 has to look at D10 downwards).
E9 SHOULD have a Lead Time of five weeks because product is scheduled to arrive in week 33 (highlighted in yellow)
For some reason the formula is not "seeing" that, but simply counting the seven weeks left in the schedule. If there were twenty weeks in the range it would show nineteen!
But the same formula in E10 -E15 produces the CORRECT result, because they are "counting down" to the inbound in week 35.
E16 should be Zero (because there are no Unfilled Orders at the end of the week) but is throwing an Error, which I think comes because the formula is reversing the "locked" and "variable" cell references and looking at rows 15 -17 (which is blank).
Any solutions, suggestions or alternatives welcome as ever.
Bookmarks