0

How can one ask Excel to record the value of all cells in a given column such that the row number is of the form row = 2^i for i in 1 to 100? Would this necessarily use VBA?

2
  • Do you mean sum all the values where the row is in the series 2^i? 2,4,8,16, etc? Sep 22, 2014 at 8:55
  • @Raystafarian Not quite. I'd like the values of those cells to be placed in, say, B1,B2,B3...Bn. Sorry about the confusion.
    – user347132
    Sep 22, 2014 at 12:41

2 Answers 2

2

Put the following formula into cell B1 and drag down.

=INDIRECT("A"&2^(ROW(A1)))

Bear in mind, this will give you a #REF! error from row 21 onwards, as per Mike Fitzpatrick's explanation.

0

If I understand you correctly you want to take the sum of values in a row, say from A1:Ax where x = 2^i. You can do that without VBA using the INDIRECT function:

=SUM(INDIRECT("A1:A"&(2^i)))

So substituting i=8 in the above formula will give you the sum of the first 8 rows in column A. The formula can easily be extended to account for ranges starting at other rows.

As far as I'm aware, Excel only supports 2^20 (1048576) rows.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .