ByCol conditional Lambda

Question

I am using the following function to calculate the sum of specific rows and I'd like to modify it to sum different rows if there's a value in the cell of a specific row as it's traversing the sequence.

=LET(duration, SEQUENCE(1,E2,COLUMN(K:K)),
projectedRevenueArray,INDEX($1:$11,{4;5;11},duration),
projectedRevenueRowSum, LAMBDA(x,INDEX(x,1)+INDEX(x,2)-INDEX(x,3)),
actualRevenueArray,INDEX($1:$11,{6;11},duration),
actualRevenueRowSum, LAMBDA(x,INDEX(x,1)-INDEX(x,2)),
arrayToUse, IF(1, projectedRevenueArray,actualRevenueArray),
rowSumToUse, IF(1, projectedRevenueRowSum,actualRevenueRowSum),
BYCOL(arrayToUse,rowSumToUse))

For now I have created an IF statement that is fixed to 1 for testing purposes and if I change the 1 to a 0 then it uses a different array and Sum and works as expected. This function lives in cell K12 and sums rows for a duration of months specified in E2.

If I input a test value in to cell K6 alone, I'd like to use that row in the calculation, rather than rows 4 and 5 for that column - a column denotes a month.

I tried the following but this makes all columns sum row 6 rather than just column K:

arrayToUse, IF(K$6=0, projectedRevenueArray,actualRevenueArray),
rowSumToUse, IF(K$6=0, projectedRevenueRowSum,actualRevenueRowSum),

Here's an example of the data I am using if that helps:

	Jan-20	Feb-20	Mar-20	Apr-20	May-20	Jun-20
Active Staff	4	8	10	10	8	4
Additonal Staff
Project Revenue	£80,000	160,000	200,000	200,000	160,000	80,000
Revenue Adjustment
Actual Revenue	£15,000
Salary Costs	£14,000	£26,208	£34,292	£34,292	£28,875	£14,167
Software Costs	£0	£0	£0	£0	£0	£0
Biz Costs	£37	£37	£37	£37	£37	£37
Costs Adjustment
Overall Costs	£14,037	£26,246	£34,329	£34,329	£28,912	£14,204
Project Profit	£963	-26245.74074	-£34,329.07	-34329.07407	-28912.40741	-14204.07407

Scott Craner · Accepted Answer · 2022-10-26T14:48:31.647

Lambda is being used incorrectly, you never define(x), so you would need to do:

LAMBDA(x,INDEX(x,1)+INDEX(x,2)-INDEX(x,3))(projectedRevenueArray)

That way x is defined, but LET would be better here:

LET(x,projectedRevenueArray,INDEX(x,1)+INDEX(x,2)-INDEX(x,3))

Then BYCOL is also being misused. VSTACK is the function to use.

Now to the actual question. You will need to pass an array to the if of the same size as the data:

IF(--INDEX($1:$11,6,duration)=0, projectedRevenueArray,actualRevenueArray)

All together we get:

=LET(duration, SEQUENCE(1,E2,COLUMN(K:K)),
    projectedRevenueArray,INDEX($1:$11,{4;5;11},duration),
    projectedRevenueRowSum, LET(x,projectedRevenueArray,INDEX(x,1)+INDEX(x,2)-INDEX(x,3)),
    actualRevenueArray,INDEX($1:$11,{6;11},duration),
    actualRevenueRowSum,  LET(x,actualRevenueArray,INDEX(x,1)-INDEX(x,2)),
    arrayToUse, IF(--INDEX($1:$11,6,duration)=0, projectedRevenueArray,actualRevenueArray),
    rowSumToUse, IF(--INDEX($1:$11,6,duration)=0, projectedRevenueRowSum,actualRevenueRowSum),
    VSTACK(IFERROR(arrayToUse,0),rowSumToUse))

To return just the sum line use:

=LET(duration, SEQUENCE(1,E2,COLUMN(K:K)),
    projectedRevenueArray,INDEX($1:$11,{4;5;11},duration),
    projectedRevenueRowSum, LET(x,projectedRevenueArray,INDEX(x,1)+INDEX(x,2)-INDEX(x,3)),
    actualRevenueArray,INDEX($1:$11,{6;11},duration),
    actualRevenueRowSum,  LET(x,actualRevenueArray,INDEX(x,1)-INDEX(x,2)),
    IF(--INDEX($1:$11,6,duration)=0, projectedRevenueRowSum,actualRevenueRowSum))

Hi Scott, unfortunately I'm getting a spill error when I enter this in to cell K12. I'm wanting it to fill out all entries in row 12 from K onwards to give the projected revenue for each month but this is creating a multi-dimensional array of data that is trying to write through data just a bit lower down in my sheet. Do you know what I need to do to adjust it please? — Automation Monkey, Oct 26 '22 at 14:45
yeah. Just tried the adjusted answer too and it works perfectly, thanks. — Automation Monkey, Oct 26 '22 at 14:51

ByCol conditional Lambda

1 Answers1