0

I am attempting to create a dynamic array spill-down with cell references that adjust as the array spills down.

Say I place 10 in cell A1 and in A2 I enter:

=IF(ROWS($1:1)>9,"",LN(10+A1^2))

and manually copy this downwards. The A1 reference will adjust as I copy downwards. So the formula in A3 will refer to the value in A2. The formula in A4 will refer to the value in A3, etc.:

enter image description here

But this is manual rather than dynamic. Another manual formula that works is:

=IF(ROWS($1:1)>9,"",LN(10+INDEX(A:A,ROWS($1:1))^2))

The only way I have succeeded is to use VBA:

Public Function propr(r As Range, N As Long)
    Dim arr, i As Long
    
    If N = 1 Then
    propr = Application.WorksheetFunction.Ln(10# + r.Value ^ 2)
         Exit Function
    End If
    
    ReDim arr(1 To N, 1 To 1)
    arr(1, 1) = Application.WorksheetFunction.Ln(10# + r.Value ^ 2)
    For i = 2 To N
        arr(i, 1) = Application.WorksheetFunction.Ln(10# + arr(i - 1, 1) ^ 2)
    Next i
    propr = arr
End Function

But I want a formula rather than a UDF.

What I have tried:

=LN(10+INDEX(A:A,SEQUENCE(9))^2)

This generates a circular reference warning:

enter image description here

This should be really easy, but I am not seeing it.

1 Answer 1

1

If you turn "Iterative Calculation" on, and reference the rows you want to fill, that will solve the Circular reference problem and your formula will Spill.

Also the If is not needed. In A2 use (of course you can use other techniques to define the range, eg INDEX)

=LN(10+A1:A9^2)

enter image description here

enter image description here

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

1 Comment

Absolutely perfect! .......................Thank you very much!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.