38

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

Jared Joke
  • 1,226
  • 2
  • 18
  • 29

3 Answers3

76

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.

NickSlash
  • 4,758
  • 3
  • 21
  • 38
  • 4
    This technique turned the trick for conditional formatting. I had an area that included a column of checkboxes. Any other value on the row was to be bolded if the corresponding checkbox was ticked. Set conditional formatting based on `=INDIRECT("AA" & ROW())` and specify `Bold` for the conditional format. – HABO Sep 28 '14 at 21:16
  • 1
    this fixed a big sorting issue I had with EPPlus, thanks! – DaFi4 Aug 05 '16 at 09:29
  • 2
    Very useful if you inserting, moving and deleting cells and want your formulas to reference cells _two columns to the left on the same row_, etc. and not the moved cells. – R. Schreurs Feb 07 '17 at 08:01
  • Also very useful if you are sending a formula to a colleague who doesn't know how to update the formula to the row they are pasting it in. – user193130 Jul 09 '20 at 21:33
37

To static either a row or a column, put a $ sign in front of it. So if you were to use the formula =AVERAGE($A1,$C1) and drag it down the entire sheet, A and C would remain static while the 1 would change to the current row

If you're on Windows, you can achieve the same thing by repeatedly pressing F4 while in the formula editing bar. The first F4 press will static both (it will turn A1 into $A$1), then just the row (A$1) then just the column ($A1)

Although technically with the formulas that you have, dragging down for the entirety of the column shouldn't be a problem without putting a $ sign in front of the column. Setting the column as static would only come into play if you're dragging ACROSS columns and want to keep using the same column, and setting the row as static would be for dragging down rows but wanting to use the same row.

Jai Govindani
  • 3,181
  • 21
  • 26
  • 1
    I'm a bit confused by the process I would go about do this then. So, would I select E1 first, then type in the formula? I did that at it seems to get a total average if I use Ctrl+selecting the columns – Jared Joke May 11 '13 at 21:11
  • Finish your formula first, then just place the cursor on any part of E1 and press F4 (you don't have to have the cell name - E1 - selected, just have the cursor in there like E|1 assuming | is the cursor) – Jai Govindani May 11 '13 at 21:33
  • please tell me what is the significance of $ sign in formula ? what $ sign does ? –  Sep 05 '21 at 07:15
  • 1
    @BabuBhatt it sets that value as 'static' which means it doesn't change when you drag/copy that formula to other cells. $C4 means "C" will remain static as it's dragged across (normally it would change/increment C > D, E, F...). On the other hand, C$4 will keep the "4" fixed as you drag/copy the formula downwards (normally it would increment 4 > 5, 6, 7...) – Jai Govindani Sep 06 '21 at 08:03
0

Look at OFFSET(). My problem was to overcome row insertions. I did the following:

=OFFSET(E774,-1,0)+OFFSET(E774,0,-1)-OFFSET(E774,0,-2)

This permits the Balance column to be above plus credit minus debit. I was hoping for an excel function to do this

NPras
  • 3,135
  • 15
  • 29
David Stack
  • 1