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 in Others by Kithuzzz
• 20,660 points
69 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 by narikkadan
• 37,660 points

Related Questions In Others

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 in Others by narikkadan
• 37,660 points
39 views
0 votes
1 answer

How do I get the current date and time in PHP?

The time would go by your server ...READ MORE

answered Feb 16 in Others by Aditya
• 7,660 points
121 views
0 votes
1 answer
0 votes
1 answer

How do I protect all worksheet in an Excel workbook with a single click?

VBA Code : Dim ws as Worksheet Dim pwd ...READ MORE

answered Oct 22 in Others by narikkadan
• 37,660 points
35 views
0 votes
0 answers

MS Excel - SumProduct formula with Loop

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

Feb 18 in Others by Edureka
• 13,640 points
84 views
0 votes
1 answer
0 votes
0 answers

How to create DropDown which have dynamic Validation List

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

Oct 31 in Others by Kithuzzz
• 20,660 points
59 views
0 votes
0 answers

Excel formula converting tan to angle

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

Oct 31 in Others by Kithuzzz
• 20,660 points
30 views
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 in Others by narikkadan
• 37,660 points
33 views
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 in Others by narikkadan
• 37,660 points
41 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