How to express cell id using string letter and current row number in an Excel formula?
You can try using INDIRECT
, which accepts a string reference to a range and returns the range itself:
=SUM(INDIRECT("A1:A"&ROW()))-SUM(INDIRECT("B1:B"&ROW()))
Here, we start with a 'stub' of "A1:A"
. We then get the current row with ROW()
(so 5 in this example) and concatenate it with our stub, giving us INDIRECT("A1:A5")
. Since INDIRECT
will return the range referenced by its argument ("A1:A5"
here), we can wrap it with the SUM
formula to get the result (which is identical to SUM(A1:A5)
). We then do the same thing for column B
.
I think you may be looking at it backwards. You need to anchor the first cell reference in the call to SUM
to the first row, but let the second cell reference change with the row. Try this in cell C1:
=SUM(A$1:A1) - SUM(B$1:B1)
Now when you copy that down the column, it becomes:
C2: =SUM(A$1:A2) - SUM(B$1:B2)
C3: =SUM(A$1:A3) - SUM(B$1:B3)
C4: =SUM(A$1:A4) - SUM(B$1:B4)
C5: =SUM(A$1:A5) - SUM(B$1:B5)
C5:= (SUM))-(SUM))
Try this:
C5:= (SUM(INDIRECT("A1:A" & ROW()))-(SUM(INDIRECT("B1:B" & ROW()))