Excel: #CALC! error (Nested Array) when using MAP, BYROW, BYCOL, MAKEARRAY and LAMBDA functions

Question

I'm often encountering very frustrating #CALC! errors when using LAMBDA inside a MAP, BYROW, BYCOL, or MAKEARRAY function. This is very similar to the following question but I found that example and solution too long to adapt it to my own situation: Excel: #CALC! error (Nested Array) when using MAP functions for counting interval overlaps

Below is a contrived but minimal example that illustrates my problem:

=LET(
    nums, SEQUENCE(25),
    rs, {10; 20},
    cs, {1, 2, 3},
    MAKEARRAY(
        ROWS(rs),
        COLUMNS(cs),
        LAMBDA(i, j,
            LET(
                r, INDEX(rs, i),
                c, INDEX(cs, j),
                SUMIFS(nums, nums, r + c)
            )
        )
    )
)

snth · Accepted Answer · 2023-05-08 12:04:13Z

1

Thanks to the minimal example I think I was able to get to the bottom of this.

The problem seems to be actually due to the SUMIFS function rather than anything else. I noticed that the documentation for SUMIFS mentions that it takes ranges (rather than an arrays) as arguments and this is what I think is behind the errors.

Unfortunately I often need to do these kinds of cross-tabulations.

EDIT: Thanks to @(David Leal) for pointing out in a comment below that my initial SUMPRODUCT formulation can be replaced with the following SUM based one:

=LET(
    nums, SEQUENCE(25),
    rs, {10; 20},
    cs, {1, 2, 3},
    MAKEARRAY(
        ROWS(rs),
        COLUMNS(cs),
        LAMBDA(i, j,
            LET(
                r, INDEX(rs, i),
                c, INDEX(cs, j),
                SUM(nums * (nums = r + c))
            )
        )
    )
)

@David Leal further provided the following link that goes into more detail about the RACON Excel Functions. This is worth reading! Thank you David!

edited May 8, 2023 at 12:04

answered May 5, 2023 at 15:50

snth

5,7564 gold badges43 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

David Leal Over a year ago

yes, it is one of the RACON Excel function, so it doesn't work for arrays. I guess is enough to use SUM instead of SUMPRODUCT(in O365 they most of the time are equivalent, and you can get. shorter formula), so you use instead. SUM(nums * (nums = r + c))

Collectives™ on Stack Overflow

Excel: #CALC! error (Nested Array) when using MAP, BYROW, BYCOL, MAKEARRAY and LAMBDA functions

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related