+ Reply to Thread
Results 1 to 5 of 5

Same formula generating inconsistent results

  1. #1
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,181

    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.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,065

    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.
    Please Avoid Joining My List Of Blocked Users by:

    Saying Please and Thank you.

    Making requests not demands.

    Checking back on your post. I will not edit any post after 4 days.

    Marking threads as closed once your issue is resolved. How? The tools at the top

    Any reputation (*) points appreciated. None of us gets paid here.

    If you found someone's input useful, please take a second to click the * at the bottom left to let them know

  3. #3
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,956

    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

    In your case columns for

    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
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    5,660

    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))
    Attached Files Attached Files

  5. #5
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,181

    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
    Last edited by Ochimus; 01-24-2021 at 03:59 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Problem with a simple Index Match formula returning inconsistent results
    By SueBristow in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-20-2017, 06:44 AM
  2. Inconsistent CSV date and cash formats generating errors
    By Ochimus in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-31-2016, 07:37 PM
  3. Inconsistent results for formula
    By Maxwel in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-27-2014, 08:04 PM
  4. [SOLVED] ERROR!!! My formula gives inconsistent Results
    By rojashan in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-27-2014, 09:11 AM
  5. [SOLVED] Inconsistent if(and(or results, HELP!!
    By Groovicles in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 08-03-2013, 11:50 AM
  6. If/VLookup/Match Formula Results Inconsistent
    By hardpenguin in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-08-2010, 05:05 PM
  7. Formula producing inconsistent results
    By joseclar in forum Excel General
    Replies: 5
    Last Post: 05-20-2009, 01:41 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1