Google Spreadsheet Formula to Use Current Row in Function

google-sheets
whoadave picture whoadave · Jul 11, 2013 · Viewed 70.4k times · Source

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

AdamL picture AdamL · Jul 11, 2013

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.

Related questions

How to highlight cell if value duplicate in same column for google spreadsheet?

I am looking for formula for google spreadsheet highlight cell if value duplicate in same column can anyone please assist me for this query?

Read More
Conditional formatting based on another cell's value

I'm using Google Sheets for a daily dashboard. What I need is to change the background color of cell B5 based on the value of another cell - C5. If C5 is greater than 80% then the background color is green …

Read More
How to get the correct range to set the value to a cell?

I want to set text or number in Google Sheet from script. I want to set Hello or number 9 in cell F2. I found this code so far: SpreadsheetApp.getActiveRange().setValue('hello'); but that doesn't specify which cell.

Read More