Google Spreadsheet Formula to Use Current Row in Function

Question

I want to calculate the product of two different columns (e.g., A & B) for each row on my spreadsheet. For a single row, this would work by using =PRODUCT(A1:B1), but I can't figure out how to get the function to use the current row instead of a manually entered row. I know you can get a reference to the current row using ROW(), but I get an error when I try something like PRODUCT(AROW():BROW()).

Answer 1

If you copy the formula you quoted into another row, or fill it down using Ctrl+D, it will automatically change the reference to the row you are copying/filling to. This is called a "relative reference". If you use the formula =PRODUCT($A$1:$B$1), this is called an "absolute reference", which will not change if copied elsewhere.

You can also use the following formula in any row:

=A:A*B:B

which will return the product of the two values in the row the formula is invoked.

Answer 2

One way to do this is using the =INDIRECT function. There are then a couple ways to approach this (plus a bonus, below):

=INDIRECT, A1B1 notation

Option 1 uses the =ROW() function (and =CONCAT to join column and row names) as you were mentioning, and in this case might look like:

=INDIRECT(CONCAT("A", ROW())) * INDIRECT(CONCAT("B", ROW())) 

(Note: I'm using x * y instead of =PRODUCT(x, y) because it seems more readable within the answer, given only two factors.)

If you did this on row 3, it would effectively come up with A3 * B3 as the formula.

=INDIRECT, R1C1 notation

Option 2 with =INDIRECT is to use a "relative" reference, using what's called "R1C1 notation". This notation is accessed by passing FALSE to the (optional) second parameter of =INDIRECT, and has two basic forms:

1. Referring to another cell by it's absolute position, e.g. R1C1 always refers to A1 (first row, first column), R3C2 always refers to B3 (third row, second column – note that the order of row and column is reversed from the B3 notation). These hold true wherever the references appear on the sheet (they'll always resolve to A1 and B3, respectively).
2. A position relative to the current cell, using R[1]C[1] for example, which indicates 1 row down, 1 row to the right. Basically, this is the same notation, except that you put the numbers in brackets to reference the number of rows and columns away from the current cell (going down and to the right for positive numbers) a particular cell is. While R[1]C[1] refers to 1 row down, 1 column to the right, R[0]C[-1] would refer to the current row (0 is no change), 1 column to the left (negative numbers go up for row and to the left for column instead of instead of down and right, respectively).

These two forms can be combined – absolute for row, relative for column, or vice versa. So if you wanted to multiply the value on the current row in column A to the value on the current row in column B, you could use:

=INDIRECT("R[0]C1", FALSE) * INDIRECT("R[0]C2", FALSE) 

This would always give you what you could think of (but not express) as =AROW() * BROW().

(Side-note: I found this description of using =INDIRECT to be helpful in coming up with the above.

---

$A$1 – anchored references (bonus)

Finally, it's probably worth noting that it's possible to reference fixed columns and/or rows using the $ modifier. Normally, when copy/pasting formulas into other cells, the column and row references change based on the amount of movement: copying a reference to =A1 from C2 to E3 would get you =C2 (A became C because the reference moved two columns to the right; 1 became 2 because it moved down 1 row). The $ notation anchors one or both of these references.

E.g. if you had a reference in to =$A1 in cell C1, and then copied that into C2, it would become =$A2. This much is unexceptional, because =A1 would have copied to =A2 as well. But if you now copied from C2 to D3 (one row down and one column right), it would become =$A3, still anchored to column A, and still changing the row number. So if from any column on row 63 (arbitrary example) you put the formula =$A63 * $B63, you could then copy/paste that to any other cell on the spreadsheet and it would multiply the values in columns A and B of the row you pasted into. (Which I think is maybe what you were trying to do?)

(Also: Note that you can do the same thing with row references - =A$1 is always row 1, but A can change; =$A$1 will always be A1, no matter where you put the reference to it.)