correct the formula as mention below..

Hi @Chaturvedi, Santosh

The formula cannot work as written because:

[calculation using mid]

is only a placeholder. It must be replaced with the actual calculation currently used in E11, but using mid instead of D9.

Also, the original REDUCE formula does not update the low and high boundaries during each iteration.

A working binary-search pattern would look like this:

=LET(
    target,E10,
    iterations,50,
    bounds,
    REDUCE(
        HSTACK(0,1000),
        SEQUENCE(iterations),
        LAMBDA(b,_,
            LET(
                low,INDEX(b,1,1),
                high,INDEX(b,1,2),
                mid,(low+high)/2,
                result,YOUR_CALCULATION_USING_MID,
                IF(
                    result>target,
                    HSTACK(low,mid),
                    HSTACK(mid,high)
                )
            )
        )
    ),
    AVERAGE(bounds)
)

The important part is:

result,YOUR_CALCULATION_USING_MID

You must replace that with the formula currently used in E11, replacing every reference to D9 with mid.

For example, if E11 currently contains:

=SUMPRODUCT(E19:E616/(1+D9)^A19:A616)

then inside the formula you would use:

result,SUMPRODUCT(E19:E616/(1+mid)^A19:A616)

There is one more important assumption: binary search only works if the result changes consistently in one direction as D9 increases. If E11 decreases when D9 increases, the comparison may need to be reversed:

IF(result>target,HSTACK(mid,high),HSTACK(low,mid))

instead of the version above.

So please post the exact formula currently in E11. Once we have that formula, we can replace the placeholder and give you a complete formula that you can paste directly into Excel.

If this answer was helpful, please consider marking it as helpful or accepting it as the answer.

Hello @Chaturvedi, Santosh

Thank you — the screenshot and explanation clarify the problem.

Since you confirmed that:

• "D9" only increases in the positive direction,
• "E11" also increases as "D9" increases,
• and the values in "E19:E616" are never negative,

then binary search is appropriate.

However, there is still one important issue in the formula shown in your screenshot.

You currently have:

result,SUM(E19:E616)

The problem is that this calculation does not use "mid".

The "LET" formula calculates a candidate value called "mid", but Excel does not temporarily place that value into cell "D9". Therefore, if the formulas in "E19:E616" depend on the actual cell "D9", "SUM(E19:E616)" will continue using the current value of D9 instead of the candidate "mid".

For example, this structure is correct for an increasing function:

=LET(

target,E10,

iterations,50,

bounds,

REDUCE(

    HSTACK(0,1000),

    SEQUENCE(iterations),

    LAMBDA(b,_,

        LET(

            low,INDEX(b,1,1),

            high,INDEX(b,1,2),

            mid,(low+high)/2,

            result,CALCULATION_USING_MID,

            IF(

                result>target,

                HSTACK(low,mid),

                HSTACK(mid,high)

            )

        )

    )

),

AVERAGE(bounds)

)

Because your result increases when D9 increases, the logic is:

IF(result>target,

HSTACK(low,mid),

HSTACK(mid,high)

)

That part is correct.

The remaining piece I need is the exact formula used in E19 (and copied down through E616).

For example, if E19 currently contains something like:

=B19/(C19+D9)

then inside the binary-search formula it must become something like:

=B19/(C19+mid)

and the complete "result" calculation can then be built using the candidate "mid".

So please post the exact formula currently contained in E19 (or whichever formula is copied down through E19:E616).

Once we have that, we can write the complete formula you can paste directly into Excel. You should not need Goal Seek anymore.

If this answer was helpful, please consider marking it as helpful or accepting it as the answer.

[Edited] OOps. Never mind. I missed that "Goal Seek" works for you.

Anyway, you would have to incorporate E19:E616 into a separate worksheet function.