CODEWITHSUNDEEP
excelexcel-formulaspill-range
Simon Gray
Simon Gray

Reputation: 1

I get a #CALC! error when I add the BYROW() function around a working formula that returns a single result?

I have a formula that creates several embeded arrays that get anyalysed to return a single result. When I feed this formula with the "concatinated string" row by row it works perfectly, as soon as I wrap the formula in the BYROW() function it gives me a #CALC! error.

I have replaced most functions within the formula with logic formulas, and have stepped through the formula variable by variable to try and identify the issue, but I cannot replicate the #Calc! error.

This is the line of Code I am trying to substitute with the BYROW() function. I will paste the entire formula below: (I have these listed as text variables so I can paste them in and out of my trials.

note4, "myLine, ($A4&"/"&$K4&"/"&$Q4),", note1, "myLineResult, BYROW($A$4#&"/"&$K$4#&"/"&$Q$4#,LAMBDA(myLine,",

Sales Data Examples Input String Examples

=LET(
  note1, "myLineResult, BYROW($A$4#&"/"&$K$4#&"/"&$Q$4#,LAMBDA(myLine,",
  note4, "myLine, ($A4&"/"&$K4&"/"&$Q4),",
  myLineResult, BYROW($A$4#&"/"&$K$4#&"/"&$Q$4#,LAMBDA(myLine,
  LET(
    myItem, TEXTBEFORE(myLine,"/",1,0),
    myPeriod, TEXTBEFORE(TEXTAFTER(myLine,"/",1,0),"/",1,0),
    myLeadTime, TEXTAFTER(myLine,"/",2,0),
    myRevWeeks, IF(myPeriod="26 Weeks",26,52),
    myRevMonths, INT(myRevWeeks*7/30),
    myFirstDate, EDATE(Sales!$B$1,-myRevMonths),
    myDateStart, IF(WEEKDAY(myFirstDate,2)>1,myFirstDate-((WEEKDAY(myFirstDate,2)-1)),myFirstDate),

    myWeekNo, SEQUENCE(myRevWeeks,1,0,1),
    myDateRange, myDateStart+(myWeekNo*7),
    myReceiptRange, myDateRange + myLeadTime,

    mySalesbyWeek, BYROW(myDateRange, LAMBDA(thisDate,
      SUM(((SalesBI[Item Id] = myItem) * SalesBI[InvoiceQuantity]) * (SalesBI[SalesInvoiceDate (bins)]>=thisDate) * (SalesBI[SalesInvoiceDate (bins)]<(thisDate+7))))),

    mySaleAvgperWeek, AVERAGE(mySalesbyWeek),
    mySalesAvgperDay, SUM(mySalesbyWeek)/(COUNT(mySalesbyWeek)*7),
    myStDev, STDEV(mySalesbyWeek),
    myAvgSalesQty, SUM(mySalesbyWeek)/SUM(--(mySalesbyWeek>0)),

    zScore,(mySalesbyWeek - mySaleAvgperWeek) / myStDev,
    myZFilter,(--(zScore>-3)*--((zScore<3))),
    note5, "myDespikedAvg, AVERAGE(FILTER(mySalesbyWeek,myZFilter=1)),",
    myDespikedAvg, (SUM(mySalesbyWeek*--(myZFilter=1))/SUM(--(myZFilter=1))),
    myDeSpikeSales, IF(myZFilter=1,mySalesbyWeek,myDespikedAvg),
    myDespikedTotal, SUM(myDeSpikeSales),


    myReceipts, BYROW(myDateRange, LAMBDA(mySalesDateRange,
      SUM((mySalesDateRange>(myReceiptRange-7))*(mySalesDateRange<myReceiptRange)*mySalesbyWeek))),

    myReciptsFinal, VSTACK(0,TAKE(myReceipts,ROWS(myReceipts)-1)),
    myRunningInv, SCAN('Weekly Normal Distribution'!$D$4,(myReciptsFinal-mySalesbyWeek),LAMBDA(a,b,a+b)),
    myInStock, ((TAKE(myRunningInv,-1)>0)*1),
  myInStock))),
myLineResult)

Upvotes: 0

Views: 50

Answers (0)

Related Questions