Forum Discussion

sophie2030's avatar
sophie2030
Copper Contributor
Aug 12, 2021

geometric progression

I am trying to repeat data in a geometric pattern e.g. repeat something in cell 1,2,4,8 repeating the same word. but I would also like it to work if I started it in row 20 then it would be 20,21,23,27,35 if anyone would know how to write a formula like this that would be great thankyou 

excel

2 Replies

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Oct 14, 2021

    sophie2030 

    Using Excel 365 or 2021.

    = LET(
  offset, ROW()-1,
  k, SEQUENCE(21,,0),
  offset+2^k)

    It finishes in row 40 with a value of 1,048,595

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Oct 14, 2021

    sophie2030 

    I was only able to write very easy VBA code to populate A1,A2,A4,A8...A131072 and B20,B21,B23,B27...B131091 with "no".

     

     

    Sub populating_by_progression()

    Dim i As Integer
    Dim j As Double
    Dim z As Double

    j = 1
    z = 1

    For i = 1 To 18


    Cells(j, 1).Value = "no"
    Cells(z + 19, 2).Value = "no"

    z = z * 2
    j = j * 2

    Next i


    End Sub