Excel: Simple formula

Factual Questions
1

I have a column in Excel where the first calculated cell is =SUM(E2:E3)/2

The last calculated cell (so far) is =SUM(E2:E37)/36

Notice that E2 remains the same every time, the other end of the range increases by one each time, and the divisor increases by one each time. When I try to copy the formula by dragging the corner the part that should remain E2 increases by one (as does the other one, which should increment). The divisor does not increment.

How do I write the formula such that when I drag the formula down the column the ‘E2’ does not increment, the end-cell does increment, and the divisor does increment?

2

=sum($e$2:e3)/count($e$2:e3)

3

Should be =(E$2:E3)/COUNT(E$2:E3). The $ in front of a column or row name indicates that it should remain fixed as the cell moves around.

4

Put a dollar sign in front of whatever you don’t want to change.

The $ locks whatever follows it

$A$1 will remain $A$1 dragged any way

$A1 will increment by 1 when dragged down

A$1 will increment columns when dragged right
On edit - see above

5

Thanks guys. I knew it was something like that, but I couldn’t remember.

OK, how do I get the divisor to increment?

6

That’s the point of using the count function.

7

You might want to use

=AVERAGE(E$2:E3)

8

I don’t know what that is. I expected the numeric value to increase for the divisor just as it does for the cell numbers.

That’s exactly what I wanted to do!

I had this number before, since I manually changed the formulas before I asked; but in any case, here’s my average miles per gallon since mid-May: 48.52 :cool:

Thanks!

9

Another way to do it is to hold ‘Ctrl’ when you drag the cells to copy them.

10

NETA: Misunderstood the OP; ‘Ctrl’ only works to keep -everything- from counting.

11

That’s exactly what the “count($e$2:e3)” part of the original suggestion does!

12

:smack: I seem to have skimmed right over that part! My attention is very divided – fragmented, really – at the moment.

13

Excel (as well as other spreadsheets) has this feature that is convenient most of the time but requires a little thought at other times. A formula referring to, say, a cell immediately to the left can be copied to a new cell, and the new cell will then refer to the cell immediately to *its * left. This is a *relative * reference. You can also use the $ to make *absolute * references to either the column, the row, or both, so that there is no change if you copy the formula to another cell.

**ultrafilter ** explained this in the third post. If you put a in front of the 2, then no matter what cell you copy the formula to in Column E, it will still refer to E2. If you copy it to Column F, it will refer to F2, because the column reference was left relative (no ). For your problem we suggested the E$2 form because all your data is in column E anyway, though $E$2 would also work.

Now with all that out of the way, copying formulas to other cells cannot ever change anything except relative cell references. So it will never change any other numeric values. You have to get a bit creative to do that sort of thing sometimes. Using the COUNT function is a straightforward and common way out of many such situations.