Forum Discussion

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Feb 20, 2022
Solved

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),
  REDUCE(0,commission,Addλ))

Any ideas?

excel
Formulas and Functions
office 365

3 Replies

    • PeterBartholomew1's avatar
      PeterBartholomew1
      Silver Contributor
      Feb 20, 2022

      SergeiBaklan 

      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!

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Feb 20, 2022

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

    Accumulateλ = LAMBDA(arr, [init], [opening],
    LET(
        acc, SCAN(init, arr, Addλ),
        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));