SOLVED

# SUM not working with result from Lambda helper fn.

Trusted Contributor

# SUM not working with result from Lambda helper fn.

I picked up a banded commission problem from another forum and tried to apply routines I had written for FIFO allocation.  It may have been a sledgehammer to crack an awkwardly shaped nut, but the Lambda function produced a list of commissions payable on each service without difficulty.

The OP was only interested in the total commission payable to the employee so I applied SUM to the result ... , and got zero!  Using REDUCE to sum the commission worked.

``````"Fail"
= LET(
commission, Allocateλ(salesAmount,Bands, Rate),
SUM(commission))

"Success"
= LET(
commission, Allocateλ(salesAmount,Bands, Rate),

Any ideas?

3 Replies

# Re: SUM not working with result from Lambda helper fn.

For what it is worth, these are the other named formulae

``````Accumulateλ = LAMBDA(arr, [init], [opening],
LET(
open, IF(ISOMITTED(opening), 1, opening),
accumulated, IF(open, acc, acc - arr),
accumulated
)
);

Addλ = LAMBDA(x, y, (x + y));

Allocateλ = LAMBDA(outputArray, inputArray, [weight],
LET(
cumulativeOutput, Accumulateλ(outputArray, 0, {0, 1}),
cumulativeInput, Accumulateλ(inputArray, 0, {0, 1}),
m, ROWS(outputArray),
n, ROWS(inputArray),
allocationArray, MAKEARRAY(
m,
n,
LAMBDA(k, h,
LET(
u, {-1, 1} * INDEX(cumulativeOutput, k, {1, 2}),
v, {-1, 1} * INDEX(cumulativeInput, h, {1, 2}),
d, SUM(IF(u < v, u, v)),
q, IF(d > 0, d, 0),
w, IF(ISOMITTED(weight), 1, INDEX(weight, h)),
q * w
)
)
),
IF(ISOMITTED(weight), allocationArray, BYROW(allocationArray, Sumλ))
)
);

Diffλ = LAMBDA(s, [s0], [d],
MAKEARRAY(
ROWS(s),
COLUMNS(s),
LAMBDA(k, h,
LET(
a, INDEX(s, k, h),
b, IF(
d,
IF(
h > 1,
INDEX(s, k, h - 1),
IF(TYPE(s0) = 64, INDEX(s0, k), s0)
),
IF(
k > 1,
INDEX(s, k - 1, h),
IF(TYPE(s0) = 64, INDEX(s0, h), s0)
)
),
a - b
)
)
)
);

Expandλ = LAMBDA(rowϑ,
LET(
n₁, ROWS(INDEX(rowϑ, 1)()),
n₂, COLUMNS(rowϑ),
result, MAKEARRAY(
n₁,
n₂,
LAMBDA(i₁, i₂, INDEX(INDEX(rowϑ, 1, i₂)(), i₁))
),
result
)
);

Sumλ = LAMBDA(x, SUM(x));

ThunkByColλ = LAMBDA(array, BYCOL(array, Thunkλ));

Thunkλ = LAMBDA(x, LAMBDA(x));``````
best response confirmed by Peter Bartholomew (Trusted Contributor)
Solution

# Re: SUM not working with result from Lambda helper fn.

This one works

``````= LET(
commission, Allocateλ(+salesAmount,Bands, Rate),
SUM(commission))``````

The difference is in +salesAmount . Auto-lifting not always works.

# Re: SUM not working with result from Lambda helper fn.

Thank you for that suggestion.  The use of REDUCE did appear as overkill and was difficult to justify.  The '+' is far neater and it appears to work used preceding any of the 3 parameters or even outside the Allocateλ function.

``= SUM(+Allocateλ(salesAmount,Bands,Rate))``

Now I just have to hope that someone contributes a more standard approach on the Chandoo forum, otherwise I may feel obliged to actually assist!