"Pete_UK" wrote in message
oups.com...
Are you likely to want to do this 65,000 times? The usual way of doing
this is to use column A to enter your values, from A2 down. In B2 you
could have this formula:

=IF(A2="","",A2+B1)

and copy this down. Column B would then give you the cumulative
(running) total until there were no more numbers in column A. In C2 you
could have this formula to count how many entries you have in column B:

=COUNT(B2:B65536)

and in D2 you could work out the average as

=MAX(B2:B65536)/C2 (assuming you are only putting in positive values)

Alternatively, you could just put numbers in column A and this formula
in cell B2:

=AVERAGE(A2:A65536)

As you add numbers to the bottom of column A, the average automatically
adjusts. This way you automatically keep track of the numbers you have
used, and if you want a running total you can put this formula in
either A1 or B1:

=SUM(A2:A65536)

Hope this helps.

Pete

Thanks Pete
My work actually goes across in rows rather than down in columns, because I
want to keep everything visible on one screen - but in principle it is the
same.
I was hoping to have 10 entries across and then divide by 10 (easy enough)
and then replace one number with another but wanted a way of increasing the
count so as to keep the average true.
Seems it can't be done
Dave

Dark Horse wrote...
"Pete_UK" wrote in message
Are you likely to want to do this 65,000 times? The usual way of doing
this is to use column A to enter your values, from A2 down. In B2 you
could have this formula:

=IF(A2="","",A2+B1)

and copy this down. Column B would then give you the cumulative
(running) total until there were no more numbers in column A. In C2 you
could have this formula to count how many entries you have in column B:

=COUNT(B2:B65536)
....

The IF formulas are unnecessary. The running sums are unnecessary in
spreadsheets unless the earlier results were wanted. Since the OP's
explanation of how s/he'd do it in BASIC wouldn't retain older running
sums and averages, why should the spreadsheet version?

The sum of all numbers in column A would be given by =SUM(A:A), or if
just from cell A2 down, =SUM(A$2:A$65536). The count and averages would
be similar, replacing SUM with COUNT or AVERAGE as needed.

Assuming you want to keep entering data into the same,
single cell and not create a column of data
and you are desperate for a solution
and not afraid of iterate...
in
3
sum
12
count
4
avg_in
3
option
3
reset
1
lock
1
Name the cells as shown.
Set iterate to 1.
Create three option buttons,
label them Reset, Set and Lock
and link them to the cell <option.
Into the <sum, <count, <avg_in, <reset and <lock cell,
enter these formulas respectively
=IF(lock=1,sum,(sum+in)*reset)
=IF(lock=1,count,(count+1)*reset)
=IF(reset=0,0,sum/count)
=IF(option=1,0,1)
=IF(option=3,1,0)
Start by clicking the Reset button and
entering a number into <in.
Click the Set button and verify that you got the desired results.
Keep entering more numbers into <in or stop and click the Lock button.
If you do not Lock it, any other recalculation on the sheet
will keep adding the last value of <in.
Always enter a new number into <in before clicking Set.

"Herbert Seidenberg" wrote in message
oups.com...
Assuming you want to keep entering data into the same,
single cell and not create a column of data
and you are desperate for a solution
and not afraid of iterate...
in
3
sum
12
count
4
avg_in
3
option
3
reset
1
lock
1
Name the cells as shown.
Set iterate to 1.
Create three option buttons,
label them Reset, Set and Lock
and link them to the cell <option.
Into the <sum, <count, <avg_in, <reset and <lock cell,
enter these formulas respectively
=IF(lock=1,sum,(sum+in)*reset)
=IF(lock=1,count,(count+1)*reset)
=IF(reset=0,0,sum/count)
=IF(option=1,0,1)
=IF(option=3,1,0)
Start by clicking the Reset button and
entering a number into <in.
Click the Set button and verify that you got the desired results.
Keep entering more numbers into <in or stop and click the Lock button.
If you do not Lock it, any other recalculation on the sheet
will keep adding the last value of <in.
Always enter a new number into <in before clicking Set.

I think it's time that I admitted defeat and went back to having hundreds of
columns across a row, because most of what has been said here went
completely over my head - and I didn't even have to duck!
Sorry guys, it probably sounds as simple as eating to you - but to me it
sounds so complicated as to be scary enough to make me wish I'd never asked.
Time to crawl back into my cave I think.
Dave