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)

In essence, I want to create a formula that finds the average values by using the current row but a certain column. Exists a way to accomplish this? Particularly to automatically scroll down the complete width of each column (assumed to be the same for all columns)?

Answer 1

Put a $ symbol in front of a row or column to make it static. As a result, if you were to drag the formula =AVERAGE($A1,$C1) along the entire page, A and C would stay the same while the 1 changed to the row that is currently being used.
A similar result can be obtained on Windows by continually hitting F4 while in the formula editing bar. The first time you hit F4, both will become static (A1 will become $A$1), after which you can only select the row (A$1) or the column ($A1). Pulling down for the entire column without adding a $ sign in front of the column shouldn't be a problem, even though it is technically doable given the formulas you already have. Setting the column as static would only be significant if you were dragging ACROSS columns and wanted to maintain using the same column. Setting the row as static would be for dragging down rows but wanting to remain using the same row.