Menu
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)?
282
Excel's INDEX function allows users to reference values in a range of data (or array of data) by their column and row number position within that range. As a simple example, the formula =INDEX(A1:F10, 4,4) would return the value in the fourth row of the fourth column in that specified data range.
The Excel ROW function returns the row number for a reference. For example, ROW(C5) returns 5, since C5 is the fifth row in the spreadsheet. When no reference is provided, ROW returns the row number of the cell which contains the formula. A number representing the row.
Format it this way The column name separated by a : (colon). Format it this way Reference the upper-rightmost cell, then the lower-leftmost cell, seperated by a : (colon). Format it this way Type the name of a previously created reference to another sheet, wrapped in curly braces.
The Excel ADDRESS function returns the address for a cell based on a given row and column number. For example, =ADDRESS(1,1) returns $A$1. ADDRESS can return an address in relative, mixed, or absolute format, and can be used to construct a cell reference inside a formula.
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.
104
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
