Excel Problems- Calculated value as a cell reference

Question

Hi, I'm relatively new to excel programming. I'm working on making a spread sheet that shows exponential decay. I have one column (A1:A1000) of 1000 random numbers between 1 & 10 using the TRUNC(RAND()*10,0) in each cell. The next Column (B1:B1000) has a logic mask =IF(A1=0,1,0) , where if the value in the A cell is 0, then the B cell shows a 1. Next, to find the number of 0's in the A column, I have the next column taking the sum of B1:B1000, which returns the number of 0's that showed up in the first column. I'm sure there's an easier way to do that, but this seems to work fine.

Here's my problem, hopefully it's clear what I'm asking:

Next, I want to take the sum of the logic column (B) from B1:B(1000- the value of the sum from (B1:1000)) in the cell below the cell that calculates sum(B1:B1000). Is there a way to to algebra in a cell formula to reference a cell? More simply, if I want to refer to A3, for example, is there a way to input something like A(2+1) to get A3? Does this make sense?

Answer 1

You can very easily do, in VBA:

ActiveCell.Formula = "=A" & (2+1) & "+15"

In Excel:

=INDIRECT(ADDRESS(2+1, COLUMN(A1)))+15

This will set the formula to "=A3+15". Generally, this is best done with variables, so remember to do that.

Cheers,
Eric