# Same formula generating inconsistent results

1. ## Same formula generating inconsistent results

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.  Register To Reply

2. ## Re: Same formula generating inconsistent results

Interesting. I enjoyed puzzling this one out.

Try this formula in E9

=MATCH(TRUE,IF(MMULT(IF(ROW(D9:D\$16)>=TRANSPOSE(ROW(D9:D\$16))=TRUE,1,0),D9:D\$16)>=C9-J\$5,TRUE,FALSE),0)-1

I noticed that you made no allowance for starting stock. That is why you see J5 in my formula.  Register To Reply

3. ## Re: Same formula generating inconsistent results

I can't help thinking this would be simplified if you kept your B3:BM5 data in a normalised data table

Date
Product
Category ' i.e. Opening Stock, Inbound, Sales, Forecast, Weeks to fulfil Order
Value

Then have a report matrix with formulae that produce the three statistics from the data table above.
i.e. rows for Date and columns for Product, S/Ratio, Unfilled Orders and L/Time  Register To Reply

4. ## Re: Same formula generating inconsistent results

Maybe try at E9

=IF(C9>SUM(D10:D\$17),"PO",IFNA(MATCH(C9-0.1,MMULT(--(ROW(D10:D\$17)>TRANSPOSE(ROW(D10:D\$17))),N(+D10:D\$17))),0))  Register To Reply

5. ## Re: Same formula generating inconsistent results

Many thanks to each for the prompt responses and solutions.

Never had a case where the same formula in the same Dataset generated inconsistent results, so this has been a first all the way.

mehmetcik, apologies if I have misunderstood the point, but the initial Opening Stock in J5 is in my calculation for the Opening stock in week 29?

Richard, appreciate the transposing Table suggestion, but can't see any advantage to the End User? I provided one SKU across eight weeks. The 'real' file has sixty SKUs covering three years, so that would be over nine thousand rows.
As it stands, 'freezing' Cols A - I and using the Week Number to pull the relevant week across, with the SKUs under each other, the planners can look down each column and see an "overview" of each element across the range at one glance. Not sure it would be as effective looking across sixty columns in the same row?

Bo_Ry, It works perfectly, but value your explanation of how looking for errors in matching the weekly Inbounds with the Unfilled Order value "-10%" solves the problem? And what is the "N" at the end if a match is found?

And thanks again, everyone.

Ochimus  Register To Reply