Spill ranges: casting arrays to intersection value with @

Question

Before Excel introduced spill ranges, before the “@” operator, one could ‘cast’ a range into a single value with “0+” (numeric values) or “""&” (strings). But “@” isn’t quite the same.

Assume that there is a column of positive integers heading south from B4; and there is a row of positive integers heading east from D2; and that columns A and C and rows 1 and 3 are completely empty.

The object is to put into D4 a single spill formula, referring to something like $B$4# and $D$2#, that, when column integer bigger than row integer, calculates the pairwise Greatest Common Divisor of the two integers. Each of the desired spill cells is to be a pairwise GCD of just two integers.

So a candidate formula is:

= IF($B$4#>$D$2#, @GCD(@$B$4#, @$D$2#), "·")

Alas GCD sees two array parameters, rather two values cast/intersected taken from the two arrays, and so calculates the GCD of all these many integers, inevitably returning 1. Sigh.

Indeed, the next few don’t even spill.

= @IF(@$B$4#>@$D$2#, GCD(@$B$4#, @$D$2#), "·")
= IF(@$B$4#>@$D$2#, @GCD(@$B$4#, @$D$2#), "·")
= @IF($B$4#>$D$2#, @GCD(@$B$4#, @$D$2#), "·")
= GCD($B$4#, $D$2#)
= GCD(@$B$4#, @$D$2#)

Suggestions please.

(Mac Excel 16.32 (19120802) — which hopefully is irrelevant.)

Thank you.

Answer 1

It will be a while before this is widely available but this can be done with a recursive LAMBDA.

Set the name

gcdArray =LAMBDA(vData,hData,vIndex,hIndex,
     LET(vSq,SEQUENCE(COUNT(vData)), 
         hSq,SEQUENCE(1,COUNT(hData)),
         g, GCD(INDEX(vData,vIndex),INDEX(hData,hIndex)),
         vFrame, IF(vIndex < COUNT(vData), IF(hIndex=1, gcdArray(vData, hData, vIndex+1, hIndex),""),""),
         hFrame, IF(hIndex < COUNT(hData), gcdArray(vData, hData, vIndex, hIndex+1),""),
         IF(vIndex=vSq,IF(hIndex=hSq,g,hFrame),vFrame)))

Then use =gcdArray(B4#, D2#, 1, 1)

This can be modified to evaluate similar 2D functions in general. Set the names

eval2Drecur =LAMBDA(func,vData,hData,vIndex,hIndex,
    LET(vSq,SEQUENCE(COUNT(vData)),
        hSq,SEQUENCE(1,COUNT(hData)),
        y, func(INDEX(vData,vIndex),INDEX(hData,hIndex)),
        vFrame, IF(vIndex < COUNT(vData),IF(hIndex = 1, eval2Drecur(func, vData, hData, vIndex+1, hIndex),""),""),
        hFrame, IF(hIndex < COUNT(hData), eval2Drecur(func, vData, hData, vIndex, hIndex+1),""),
        IF(vIndex=vSq,IF(hIndex=hSq,y,hFrame),vFrame))) 

 nameGCD =LAMBDA(x, y, GCD(x,y))

Then call =eval2Drecur(nameGCD, B4#, D2#, 1, 1)

Update for MAKEARRAY function

MAKEARRAY is still in beta. When it's released, it will simplify the answer.

=MAKEARRAY(ROWS(B4#),COLUMNS(D2#),LAMBDA(a, b,
     LET(c,INDEX(B4#,a),
         d,INDEX(D2#,b),
         IF(c>d, GCD(c, d), "·"))))