Run out Date Formula

[Jacko320]

Hey everyone! I'm needing some direction on calculating a run out date for some products I have. I will have screenshots below.

I have up to 200 open orders on some items, I just can't seem to wrap my brain around an equation that will solve this for me. Just needing to figure out at what order/date the available inventory will equal 0.

Any advice would be greatly appreciated!!

[#2]

@Jacko320 

As the IF function can only be nested 64 levels, using that (a "brute force" technique) won't meet your needs. (And it would be as ugly as heck to write and debug if it did work.)

The calculation must iterate over the pairs of quantity and date cells until, say, it finds an Open Order Date cell that does not contain a date. So until recent years, you would have to either add many helper cells for the calculations, or calculate it in a VBA procedure (or possibly in Office Script, with which I am not familiar).

If your version of Excel supports the LAMBDA function, you can define a LAMBDA function that does a calculation and conditionally (i.e., if run out has not occurred) calls itself with different arguments. (A function that calls itself is called a recursive function.)

I have created the function CheckForRunOutR (the R just reminds me that it is for checking data chronologically to the right). Feel free to use a different name, but remember to change it inside the LAMBDA formula (as well as the formula name definition, and in cell formulas).

=LAMBDA(Remaining,NextQty,NextDt,
    IF(AND(ISNUMBER(NextQty),NextQty>=0),
        IF(Remaining-NextQty<=0, NextDt,
            CheckForRunOutR(Remaining-NextQty,OFFSET(NextQty,0,2),OFFSET(NextDt,0,2))),
        IF(ISNUMBER(NextDt),
            "-bad data in "&CELL("address",NextQty),
            ">"&TEXT(OFFSET(NextDt,0,-2),"mm/dd/yy")
        )
    )
)

All those extra spaces and soft line feeds are just present to make it easier to read and understand.

If there will be no runout with the open orders, I have the function prepend ">" to the last date. Alternatively, you could have it just output "-none-" or another text literal or a date-in-the-far-future literal.

Note that I did not include logic to handle any cases where:

• the Open Order Dates are not in chronological order, or
• the initial quantity is zero or negative, or
• a non-integer quantity was specified where an integer was required (My formula would not know which SKUs require an integer quantity.)

But I did include logic to flag a missing or nonnumeric or negative OO Qty if the OO Date is present. The function permits a zero OO Qty (NextQty>=0, rather than NextQty>0) to allow you to test a what-if-an-order-is-cancelled condition manually with ease.

As in the attached workbook, I have not tested it beyond twenty open orders. But I do not expect problems (other than slow computations) with many more columns of open orders.

Regarding your data design: Is it a significant problem to maintain the data in the layout you showed? I would have put the open order occurrences into their own rows (not their own columns) in a separate worksheet. I believe it would be easy to change the LAMBDA function above to accommodate that alternative.

[#3]

Hi

Base on SnowMan workbook

=IFERROR(LET(p,F2:AS2,INDEX(p,,SUM((SCAN(0,p*ISEVEN(COLUMN(p)),LAMBDA(c,x,c+x))<B2)*1)+2)),"")

@Jacko320 

[#4]

@Hecatonchire Thank you so much for helping me out with this! Unfortunately, I am having a hard time setting the parameters for the equation to my data. Would you be able to help me out with translating your equation to the data I will attach? I would appreciate it greatly!

[#5]

@SnowMan55 Thank you so much for helping me out with this! Unfortunately, I am having a hard time setting the parameters for the equation to my data. Would you be able to help me out with translating your equation to the data I will attach? I would appreciate it greatly! I haven't run into any problems yet of having my data arrayed in Columns, I guess I'll cross that bridge when I get there.

[#6]

I'm Specifically having a hard time understanding what the "p's" and "C's" contribute to the function.

[#7]

As your table is shifted of a column compared to that of SnowMan55

In E2 :

=IFERROR(LET(p,G2:AS2,INDEX(p,,SUM((SCAN(0,p*ISEVEN(COLUMN(p)),LAMBDA(c,x,c+x))<C2)*1)+2)),"")

Here the table is supposed to stop at column AS. It's up to you to adapt

The SCAN function may not be available (#Name)

@Jacko320 

[#8]

@Jacko320 

I notice now that your column headers for "Open Order # 3 Date" and "Open Order # 4 Quantity" are missing. That error can be avoided if you replace all those open order column headers with Excel formulas. So as long as the Quantity and Date values begin in column G, you can use this formula:

=LET( ColNum, COLUMN(), OrderNum, FLOOR.MATH((ColNum-5)/2),
"Open Order # " & OrderNum & IF(ISODD(ColNum)," Quantity"," Date") )

I put information on the LET function into the _Info worksheet of the attached, updated workbook. With most built-in Excel functions, Microsoft determines the calculations that occur. With the LET and LAMBDA functions, you (the author) determine what calculations are performed.

And that relates to your question about the p's and c's. The first p in Hecatonchire's LET function is a variable name, a term familiar to programmers. The person writing the formula decides what calculation is made using that variable. The variable is used within that instance of the LET function only. (But the same variable name can be used in other formulas, with perhaps a different meaning there). The variable's value (a number or text value or cell reference or range reference or…) is specified by the second parameter, optionally followed by a third parameter as a second variable name, a fourth parameter as the value of the second variable, etc. The last parameter for the LET function will be an expression (either a calculation or a repetition of a variable name) that Excel should use as the value for the cell (or for calculation, if the LET function is wrapped inside other calculations).

The c inside the LAMBDA function is like a variable, but instead of its value being determined within that LAMBDA function, its value is determined by whatever expression calls (invokes) that LAMBDA function.

Now, to some specifics. If you have my LAMBDA formula defined in the Name Manager with the name CheckForRunOutR, then the formula for cell D2 (or any available cell in row 2) is shown on worksheet "data from JB":

=CheckForRunOutR(C2,G2,H2)
(C2 is the cell it is to use for Remaining, G2 is the cell is the cell it is to use for NextQty, and H2 is the cell it is to use for NextDt.)

Copy it down as needed.

As for Hecatonchire's formula, for it to work with your data layout, you have to use the ISODD function instead of the ISEVEN function. The formula's p could instead be named OpenOrderDataForSKU (a bit more typing, but it adds meaning). See the "data from JB" and _Info worksheets, respectively, for the example and for more information. To use this formula, you would need to change the reference to column AS (or AT or whatever) to be a reference to the last column in which you will put open order data – that's column OP, if you are going to support 200 open orders. This formula is more difficult to understand than mine, but it does not involve the Name Manager, and it may calculate results faster. OTOH, you are limited in what you can display if no run out is projected.