Thread: Simple Fx question Thanks: 0 Likes: 0

1. Simple Fx question

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.  Reply With Quote

2. Re: Simple Fx question

Try this.

=AVERAGE(INDEX(A:A,1):INDEX(A:A, 4))  Reply With Quote

3. Re: Simple Fx question

@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.  Reply With Quote

4. Re: Simple Fx question

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

5. Re: Simple Fx question Originally Posted by thisoldman @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.  Reply With Quote

User Tag List

Tags for this Thread

delete, figures, reads, row, set  Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•