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)?

Answer 1

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.