Forum Discussion

Viz's avatar
Viz
Brass Contributor
Jan 24, 2020

One of my offset formula does not return dynamic array but works as regular array

I was working on creating an offset function that would take its own previous value and add a few percentage growth points. It worked fine when it as a regular array (Shift + CTRL + Enter) but it did not work as a dynamic array. In that case it gave a circular reference error.

 

Here is what I did.

 

Cell A3 = 1,000

Cell B3 = Offset(A1,0,0,1,10)*(1+5%)

 

When I entered it as a dynamic array, it gave a circular reference error. However, when I tried the following, it worked fine:

 

Cell A1 = 1,000

Cell B1:J1 = Offset(A1,0,0,1,10)*(1+5%) and entered it as an array, it worked fine.

 

It would be great if someone can tell how to handle this using dynamic array.

1 Reply

  • Hi Viz 

     

    I might handle it like this using SEQUENCE to generate the number of columns required rather than Offset

     

    =A4*(1.05^SEQUENCE(1,10))

     

    or making a bit more dynamic

    =A4*(A3^SEQUENCE(1,B3))

     

Resources