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 Answers
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.
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.