Formula to stop calculations once result hits zero

[TimLovell]

Good Morning,

Excel newbie here, trying to sort this out.

I have a series of calculations running on IF statements that I want to terminate once the result hits zero. Using previous calculations and a SEQUENCE command to lay out the dates for production, I am trying to list an estimated daily production amount over those days, but need the list to stop once it hits zero.

Current formulas:

In F22 = =IF(G21<F21,G21,F21)

In G22 = =G21-F22

I need them to only calculate into the range of dates that get listed with the SEQUENCE command under Production Estimate.

Thank you.

 

[Cubist]

Edit:
What is the formula for the SEQUENCE?

 

[TimLovell]

I am using =WORKDAY.INTL(B5-1,SEQUENCE(B7),"0000011")

Where B5 is input manually as the production start date, and B7 is calculated with =SUM(INT((WEEKDAY(B5-{2,3,4,5,6})+B4-B5)/7)) where B4 is input manually as due date.

 

[Cubist]

One more question: What if the calculation doesn't hit 0 before the end of the sequence? Do you want to keep going or stop by the end of the sequence?

 

[TimLovell]

One more question: What if the calculation doesn't hit 0 before the end of the sequence? Do you want to keep going or stop by the end of the sequence?

In that case I would want it to stop by the end of the sequence. Of course, if that happens I have made other critical errors in calculation that I would need to address.

 

[Cubist]

I couldn't think of a simple solution, so here's a recursive lambda.
In your name manager, add new name and call it "myLambdaFunction" and then insert this in the Refer to

=LAMBDA(start_value1, start_value2,
    LET(
        steps,
        IF(
            start_value1 <= start_value2,
            TEXT(start_value1, "0") & ", " & TEXT(start_value2, "0"),
            TEXT(start_value1, "0") & ", " & TEXT(start_value2, "0") & ", " & myLambdaFunction(start_value1 - start_value2, start_value2)
        ),
        IF(
            start_value1 <= start_value2,
            steps,
            steps & ", 0"
        )
    )
)

Test_Book3.xlsx
	A	B	C	D	E
2
3
4		5/24/24
5		5/20/24
6
7		5
8
9
10
11
12
13
14
15			1092	2658
16		5/20/24	2658
17		5/21/24	1092
18		5/22/24	1566
19		5/23/24	474
20		5/24/24	0
Sheet2

Cell Formulas
Range	Formula
B7	B7	=SUM(INT((WEEKDAY(B5-{2,3,4,5,6})+B4-B5)/7))
B16:B20	B16	=WORKDAY.INTL(B5-1,SEQUENCE(B7),"0000011")
C16:C20	C16	=TAKE(UNIQUE(--TEXTSPLIT(myLambdaFunction(D15,C15),,", ")),B7)
Dynamic array formulas.

 

[TimLovell]

I am getting a #SPILL error listed in that cell. I modified the locations to match the ones on my sheet (In this case, D15 and C15 were replaced with G21 and F21).

I've never used the Name Manager before, but I followed the directions on naming and cut and paste the formula into the "Refers to" field.

 

[Joe4]

I am getting a #SPILL error listed in that cell

Do you understand how Spill functions work?
You just place the formula in one single cell, and the answer will "spill" into the other cells (usually rows below but also can be columns to the right).
So the cells that it is trying to spill over into must be empty.
If you are getting a #SPILL error, it means that you already have something in one of the cells it is trying to spill over into.

 

[TimLovell]

Got it - thank you. As mentioned - Excel newbie here - did not know what SPILL was until you just explained it. Appreciate the help.

 

[Joe4]

You are welcome.

So, did Cubist's solution solve your problem?
If so, you can mark his post as the solution.