In excel how do I reference the current row but a specific column?
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
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.