Excel Labs blank screen when selecting a cell with complex formula

[christianLarney]

A (rather lengthy) formula, using multiple levels of LET, LAMBDA, TAKE, XMATCH, MAP and a few others, causes Excel labs tab to go blank when I click on the cell. When I attach a debugger, I see the error below. The formula continues to evaluate correctly, but no longer displays in Excel Labs. I have Excel Version 2402 (Build 17328.20162) and Excel Labs 1.3.0.0
 

[christianLarney]

Oh, the formula debugger really doesn't like my work. It previously complained my formula was too complicated, but now just won't work at all. If I disable the formula debugger before selecting the cell the issue goes away.

[jack-williams]

@NWilson

[NWilson]

Hi Christian, I'm sorry the debugger isn't working for your formula. Are you able to share what the contents of the formula, to help us troubleshoot the issue?

[NWilson]

@christianLarney Hello! Are you able to share the formula that's causing problems? I'd like to be able to reproduce & fix this.

[christianLarney]

Hi Nicholas, Apologies for the slow response. Are you able to work with the formulae in isolation? Below are two variations that are causing problems. Let me know if you need the surrounding worksheet for context and I'll see if I can reduce it to a version I can make public. Thanks, Christian.

=LET(
fundMonthsHistory, $G$8,
statisticMonthsDuration, BO$2,
refDate, $G$2,
IF(
OR(
fundMonthsHistory < statisticMonthsDuration,
AND(fundMonthsHistory = statisticMonthsDuration, DAY(refDate) = 1)
),
"",
LET(
usedExcessReturnTargets, LET(
totalMonthsCount, $G$3,
finalMonthProportion, $AO$14,
excessReturnTargets, LET(
fundBenchmarkTitles, $M$20:$AD$20,
allFundBenchmarks, TAKE($M$23:$AD$500, totalMonthsCount),
benchInd, XMATCH(Portfolio, fundBenchmarkTitles),
TAKE(CHOOSECOLS(allFundBenchmarks, benchInd + 1), -statisticMonthsDuration)
),
monthlyExcessReturnTargets, ((1 + excessReturnTargets) ^ (1 / 12)) - 1,
withFinalMonthAdjustment, IF(
statisticMonthsDuration = 1,
monthlyExcessReturnTargets * finalMonthProportion,
VSTACK(
DROP(monthlyExcessReturnTargets, -1),
TAKE(monthlyExcessReturnTargets, -1) * finalMonthProportion
)
),
withFinalMonthAdjustment
),
excessReturnTarget, PRODUCT(1 + usedExcessReturnTargets) - 1,
excessReturnTarget
)
)
)

=LET(
fundMonthsHistory, $G$8,
statisticMonthsDuration, BO$2,
refDate, $G$2,
IF(
OR(
fundMonthsHistory < statisticMonthsDuration,
AND(fundMonthsHistory = statisticMonthsDuration, DAY(refDate) = 1)
),
"",
LET(
usedBenchmarkReturns, LET(
totalMonthsCount, $G$3,
benchmarkTitles, LET(
fundBenchmarkTitles, $M$20:$AD$20,
allFundBenchmarks, TAKE($M$23:$AD$500, totalMonthsCount),
benchInd, XMATCH(Portfolio, fundBenchmarkTitles),
TAKE(CHOOSECOLS(allFundBenchmarks, benchInd), -statisticMonthsDuration)
),
benchmarkReturnHeadings, $AN$21:$AT$21,
benchmarkColInd, MAP(benchmarkTitles, LAMBDA(t, XMATCH(t, benchmarkReturnHeadings))),
allBenchmarkReturns, TAKE($AN$23:$AT$500, totalMonthsCount),
statisticBenchmarkReturns, TAKE(allBenchmarkReturns, -statisticMonthsDuration),
MAP(
SEQUENCE(statisticMonthsDuration),
benchmarkColInd,
LAMBDA(r, c, INDEX(statisticBenchmarkReturns, r, c))
)
),
benchmarkReturn, PRODUCT(1 + usedBenchmarkReturns) - 1,
benchmarkReturn
)
)
)

[jack-williams]

Second one repro'd for me when trying to open the debugger - assertion failure

[NWilson]

@christianLarney Thank you ever so much! Yes, I can reproduce the issue now. I would expect this to be fixed in a future release of Labs.

We are sorry it has taken so long to discover and act on the bug, and for the inconvenience.