Simple Fx question

[brose99]

If I have the following data set:

.....A...
1 2.9
2 0.7
3 -0.8
4 -2.5

In cell A6, I want to sum all of A1 thru A4 and average it out =SUM(A1:A4)/4

but...when I add another row of figures at the top of the set and delete a line of figures at the bottom of the set,
for example, now I have:

.....A...
1 -2
2 2.9
3 0.7
4 -0.8


I now have a Fx in A6 that reads =SUM(A2:A4)/4 because I added a row of figures in row 1 and deleted a row of figures in row 4.

I want the Fx in A6 to stay the same when I add and delete rows of information so it always reads without me having to manually go back and change A2 to A1 each time.

Thanks in advance for your help.

 

[thisoldman]

Try this.

=AVERAGE(INDEX(A:A,1):INDEX(A:A, 4))

 

[joeu2004]

@brose99.... Assuming you have nothing (unrelated) below the column of numbers to be averaged, use =AVERAGE(A1:A1000), assuming you might have as many as 1000 numbers sometimes. The AVERAGE function ignores empty cells in ranges. Change 1000 to whatever you think is a reasonable upper limit. Avoid using A:A.

 

[thisoldman]

@joeu2004, I also considered a solution like yours but brose99 wrote that the formula was in A6.

 

[joeu2004]

@joeu2004, I also considered a solution like yours but brose99 wrote that the formula was in A6.

Thanks. I looked for that, but I overlooked it.

In that case, I would write =AVERAGE(A1:A5), assuming that A5 is an always-empty cell to separate the average from the data. As we insert between A1 and A5, the range A1:A5 is changed automagically.

Alternatively, =AVERAGE(INDEX(A:A,1):INDEX(A:A,ROW()-1). That allows cells to be inserted above A1 as well as above A6.

The range INDEX(A:A,1):INDEX(A:A,4) does not ease the maintenance burden. The number 4 might still need to be modified manually.