In excel how do I reference the current row but a specific column

Question

Let s say I had the datasheet  A  B  C  D ----------- 5  4  6  3 4  4  3  2 5  4  6  2   And I wanted to do something like    A  B  C  D  E                  F ---------------------------------------------- 5  4  6  3   AVERAGE A1 C1      AVERAGE B1 D1  4  4  3  2   AVERAGE A2 C2      AVERAGE B2 D2  5  4  6  2   AVERAGE A3 C3      AVERAGE B3 D3    So basically I want to make a formula that uses the current row but a specific column to find the average values  Is there a way to do this  Especially to automatic it down the entirety of each column  assuming all the columns are the same height

User · Answer

If you dont want to hard-code the cell addresses you can use the ROW() function.

eg: =AVERAGE(INDIRECT("A" & ROW()), INDIRECT("C" & ROW()))

Its probably not the best way to do it though! Using Auto-Fill and static columns like @JaiGovindani suggests would be much better.

User · Answer

To static either a row or a column  put a   sign in front of it  So if you were to use the formula  AVERAGE  A1  C1  and drag it down the entire sheet  A and C would remain static while the 1 would change to the current row  If you re on Windows  you can achieve the same thing by repeatedly pressing F4 while in the formula editing bar  The first F4 press will static both  it will turn A1 into  A 1   then just the row  A 1  then just the column   A1   Although technically with the formulas that you have  dragging down for the entirety of the column shouldn t be a problem without putting a   sign in front of the column  Setting the column as static would only come into play if you re dragging ACROSS columns and want to keep using the same column  and setting the row as static would be for dragging down rows but wanting to use the same row

[excel] In excel how do I reference the current row but a specific column?

Examples related to excel

Examples related to excel-formula

Examples related to excel-2010