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

Oct 15, 2022 in Others 1,237 views

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.
• 63,420 points

How do I get an Excel range using row and column numbers in VSTO / C#?

Use: int countRows = xlWorkSheetData.UsedRange.Rows.Count; int countColumns = xlWorkSheetData.UsedRange.Columns.Count; object[,] ...READ MORE

How do I transpose a column to a matrix in Excel?

With data in column A, pick some cells ...READ MORE

How do you populate a google sheets/excel column with cells from a column in another sheet in the same document?

You have two options on chronology: sheet-by-sheet =QUERY({Sheet1!X:Z; Sheet2!X:Z; ...READ MORE

How do I use the Indirect Function in Excel VBA to incorporate the equations in a VBA Macro Function

Try this: Sub Test() Dim str As String: str ...READ MORE

MS Excel - SumProduct formula with Loop

1 I have 4 arrays of data where ...READ MORE

Excel function for divide or split number to maximum possible equal parts

The underlying math for this is as ...READ MORE

How to create DropDown which have dynamic Validation List

In my data table, the columns "Category" ...READ MORE