Automatic formula changer?

Hi!
How do I get so that I won´t have to change the formula if I add a cell to the formula?

A1=5
A2=10
B1=(A1+A2)/2

add 1 cell.


A1=5
A2=10
A3=2
B1=(A1+A2+A3)/3

How do I get Excel to automaticly realize that there has come a new cell AND that Excel should change the formula.

Is it possible?

=AVERAGE(1:1)

This will average all non-blank cells in row 1.

Originally Posted by jasoncw

=AVERAGE(1:1)

This will average all non-blank cells in row 1.

Ok, but what If I want the average to be at the end of row 1?

Unless you know how many cells you might need to input (e.g. 20), then it would be a little difficult to accomplish this. It would be much easier to include the average in column A for the rest of the columns, and use:

=AVERAGE(B1:Z1)

or how many columns you would like (they go up to column IV).

If you know that you will never use more than say 10 amounts, use the average formulas in all rows in column K (the 11th column), and use:

=AVERAGE(A1:J1)

HTH

Jason

Oh, sure, you could definitely use dynamic ranges to determine the average range, but without VBA, I really don't know how to "move" the average result to the end of the dynamic range.

In other words, for ato's example, if there are numbers in cells A1, A2, and A3, with the average in A4, I don't know of a way to "move" the average from A4 automatically if another number is entered in row 1.