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

0 votes

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 by Kithuzzz
• 38,010 points
1,300 views

1 answer to this question.

0 votes
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.
answered Oct 15, 2022 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer

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

answered Nov 17, 2022 in Others by narikkadan
• 63,420 points
1,265 views
0 votes
1 answer

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

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

answered Nov 25, 2022 in Others by narikkadan
• 63,420 points
1,489 views
0 votes
1 answer

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

answered Dec 19, 2022 in Others by narikkadan
• 63,420 points
1,136 views
0 votes
1 answer

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

answered Jan 19, 2023 in Others by narikkadan
• 63,420 points
637 views
0 votes
0 answers

MS Excel - SumProduct formula with Loop

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

Feb 18, 2022 in Others by Edureka
• 13,670 points
254 views
0 votes
1 answer

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

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

answered Oct 17, 2022 in Others by narikkadan
• 63,420 points
1,908 views
0 votes
0 answers

How to create DropDown which have dynamic Validation List

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

Oct 31, 2022 in Others by Kithuzzz
• 38,010 points
417 views
0 votes
0 answers

Excel formula converting tan to angle

I have an excel spreadsheet that computes ...READ MORE

Oct 31, 2022 in Others by Kithuzzz
• 38,010 points
223 views
0 votes
1 answer
0 votes
1 answer

How do I combine the first character of a cell with another cell in Excel?

Try this: =CONCATENATE(LEFT(A1,1), B1) READ MORE

answered Nov 7, 2022 in Others by narikkadan
• 63,420 points
899 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP