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

%3CLINGO-SUB%20id%3D%22lingo-sub-1126544%22%20slang%3D%22en-US%22%3EOne%20of%20my%20offset%20formula%20does%20not%20return%20dynamic%20array%20but%20works%20as%20regular%20array%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1126544%22%20slang%3D%22en-US%22%3E%3CP%3EI%20was%20working%20on%20creating%20an%20offset%20function%20that%20would%20take%20its%20own%20previous%20value%20and%20add%20a%20few%20percentage%20growth%20points.%20It%20worked%20fine%20when%20it%20as%20a%20regular%20array%20(Shift%20%2B%20CTRL%20%2B%20Enter)%20but%20it%20did%20not%20work%20as%20a%20dynamic%20array.%20In%20that%20case%20it%20gave%20a%20circular%20reference%20error.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20what%20I%20did.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECell%20A3%20%3D%201%2C000%3C%2FP%3E%3CP%3ECell%20B3%20%3D%20Offset(A1%2C0%2C0%2C1%2C10)*(1%2B5%25)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20entered%20it%20as%20a%20dynamic%20array%2C%20it%20gave%20a%20circular%20reference%20error.%20However%2C%20when%20I%20tried%20the%20following%2C%20it%20worked%20fine%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECell%20A1%20%3D%201%2C000%3C%2FP%3E%3CP%3ECell%20B1%3AJ1%20%3D%20Offset(A1%2C0%2C0%2C1%2C10)*(1%2B5%25)%20and%20entered%20it%20as%20an%20array%2C%20it%20worked%20fine.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20would%20be%20great%20if%20someone%20can%20tell%20how%20to%20handle%20this%20using%20dynamic%20array.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1126544%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1126573%22%20slang%3D%22en-US%22%3ERe%3A%20One%20of%20my%20offset%20formula%20does%20not%20return%20dynamic%20array%20but%20works%20as%20regular%20array%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1126573%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F530769%22%20target%3D%22_blank%22%3E%40Vishwa1935%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20might%20handle%20it%20like%20this%20using%20SEQUENCE%20to%20generate%20the%20number%20of%20columns%20required%20rather%20than%20Offset%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DA4*(1.05%5ESEQUENCE(1%2C10))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eor%20making%20a%20bit%20more%20dynamic%3C%2FP%3E%0A%3CP%3E%3DA4*(A3%5ESEQUENCE(1%2CB3))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F166994iD5577775932F9854%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_0.png%22%20title%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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))

 

clipboard_image_0.png