6

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.

jdaw1
  • 225
  • 3
  • 11
  • what is the # operator? – Forward Ed Jan 05 '20 at 19:44
  • 1
    @Forward Ed it's the [Spill Range Operator](https://support.office.com/en-us/article/spilled-range-operator-3dd5899f-bca2-4b9d-a172-3eae9ac22efd?ui=en-US&rs=en-NZ&ad=NZ) – chris neilsen Jan 05 '20 at 23:12
  • @chrisneilsen Thank you. My google search skills for Excel # operator were not working for me – Forward Ed Jan 05 '20 at 23:34
  • 1
    do you need to use ranges if you want to refer to single cells? why not just `=IF($B4>D$2,GCD($B4,D$2), "·")` and copy it across the whole section? – Stachu Jan 13 '20 at 12:30
  • 2
    I doubt GCD will be able to take arrays and return an array, it just is not designed that way. – Scott Craner Jan 13 '20 at 21:36
  • 1
    Just like MIN and MAX will also not return a pairwise array. MAX(B4#,D2#) will return the max number in the array and not an array of Max between the pairs. It is not going to happen. – Scott Craner Jan 13 '20 at 21:47
  • 1
    @Stachu: because then I need to choose the size of the array in advance, undermining the whole purpose of spill. – jdaw1 Jan 13 '20 at 23:06
  • @Scott Craner: I could not make it work. Perhaps there isn’t yet a truly general syntax for spill functions. – jdaw1 Jan 13 '20 at 23:08
  • 4
    @jdaw1 as I stated GDC,MAX,MIN, and a handful of others take array of numbers and return a single value. They are not, nor will they be setup in the future for pairwise comparison. The only way you are going to do this is to write your own UDF. – Scott Craner Jan 13 '20 at 23:11
  • Well, the answer is that there is no answer, not even for a bounty. Thank you for considering the question. Microsoft: heed! – jdaw1 Jan 19 '20 at 16:06

1 Answers1

2

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), "·"))))
Axuary
  • 1,497
  • 1
  • 4
  • 20
  • Thank you for that. Spreadsheets are not a geeky coding environment. Spreadsheets are a legible comprehensible mixed environment. Your solution might work, but non-spilling =GCD($B4,D$2) would be hugely more comprehensible. – jdaw1 Feb 24 '21 at 21:08
  • @jdaw1 did you try this? For me it actually works (ie it does spill)! – chris neilsen May 20 '21 at 03:40