Entering formula in A1 mode works but in R1C1 mode fails

[Clif McIrvin]

First, I apologize if this has already been addressed. I was unsuccessful at trying to find anything similar.
I have a working formula to average the bottom three values in a filtered list.
My VBA routine which builds the spreadsheet from scratch (reading data from individual files) fails in this loop:

    For ix = colAvg To LastDefinedCol
        dest.Cells(NextRow, ix).FormulaR1C1 = AllHistoryFormulas(ix)
    Next ix

Where ix is the current column number, and AllHistoryFormulas() is an array of formula text.
The code fails when it reaches this formula with:
Error Number 1004
Application-defined or object-defined error
When I attempt to enter this formula in the R1C1 formula bar I get "You've entered too few arguments for this function" with the cursor at the end of the LAMBDA function:

Here's my working formula as shown in the R1C1 formula bar:

=AVERAGE(TAKE(FILTER(R16C[-4]:RC[-4],MAP(R16C[-4]:RC[-4],LAMBDA(r,--(SUBTOTAL(103,r)=1)))=1),-3))

And, the same formula in the A1 formula bar:

=AVERAGE(TAKE(FILTER(R$16:R19,MAP(R$16:R19,LAMBDA(r,--(SUBTOTAL(103,r)=1)))=1),-3))

Clear as mud?
What am I doing wrong?
I'm using .FormulaR1C1 because the formula text does not change with the cell address.

 

[Clif McIrvin]

Interesting. I disabled error checking, and I get the expected result!
That is, I changed that loop to

    On Error Resume Next
    For ix = colAvg To LastDefinedCol
        dest.Cells(NextRow, ix).FormulaR1C1 = AllHistoryFormulas(ix)
    Next ix
    On Error GoTo 0

 

[Clif McIrvin]

Interesting. I disabled error checking, and I get the expected result!

I spoke too soon. Finding the formula in the cell with disabled error handling must have been a fluke - perhaps I had manually copied the formula into that cell before running the code.
I just ran the code several times in the process of verifying other parts of the code, and now I'm getting what I expected with the disabled error handling: the loop completes and leaves this particular cell empty.

 

[Alex Blakenburg]

The use of "r" as a variable seems to be confusing it. I changed r to rng and it appears to have fixed it.

=AVERAGE(TAKE(FILTER(R16C[-4]:RC[-4],MAP(R16C[-4]:RC[-4],LAMBDA(rng,--(SUBTOTAL(103,rng)=1)))=1),-3))

 

[Clif McIrvin]

The use of "r" as a variable seems to be confusing it. I changed r to rng and it appears to have fixed it.

Great catch!
It makes sense, now that I think about it.
Thanks!

 

[Alex Blakenburg]

You're welcome.