software

quot;Pete_UKquot; gt; wrote in message oups.com...
gt; Are you likely to want to do this 65,000 times? The usual way of doing
gt; this is to use column A to enter your values, from A2 down. In B2 you
gt; could have this formula:
gt;
gt; =IF(A2=quot;quot;,quot;quot;,A2 B1)
gt;
gt; and copy this down. Column B would then give you the cumulative
gt; (running) total until there were no more numbers in column A. In C2 you
gt; could have this formula to count how many entries you have in column B:
gt;
gt; =COUNT(B2:B65536)
gt;
gt; and in D2 you could work out the average as
gt;
gt; =MAX(B2:B65536)/C2 (assuming you are only putting in positive values)
gt;
gt; Alternatively, you could just put numbers in column A and this formula
gt; in cell B2:
gt;
gt; =AVERAGE(A2:A65536)
gt;
gt; As you add numbers to the bottom of column A, the average automatically
gt; adjusts. This way you automatically keep track of the numbers you have
gt; used, and if you want a running total you can put this formula in
gt; either A1 or B1:
gt;
gt; =SUM(A2:A65536)
gt;
gt; Hope this helps.
gt;
gt; Pete
gt;
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...
gt;quot;Pete_UKquot; gt; wrote in message
gt;gt;Are you likely to want to do this 65,000 times? The usual way of doing
gt;gt;this is to use column A to enter your values, from A2 down. In B2 you
gt;gt;could have this formula:
gt;gt;
gt;gt;=IF(A2=quot;quot;,quot;quot;,A2 B1)
gt;gt;
gt;gt;and copy this down. Column B would then give you the cumulative
gt;gt;(running) total until there were no more numbers in column A. In C2 you
gt;gt;could have this formula to count how many entries you have in column B:
gt;gt;
gt;gt;=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 lt;optiongt;.
Into the lt;sumgt;, lt;countgt;, lt;avg_ingt;, lt;resetgt; and lt;lockgt; 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 lt;ingt;.
Click the Set button and verify that you got the desired results.
Keep entering more numbers into lt;ingt; 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 lt;ingt;.
Always enter a new number into lt;ingt; before clicking Set.
quot;Herbert Seidenbergquot; gt; wrote in message oups.com...
gt; Assuming you want to keep entering data into the same,
gt; single cell and not create a column of data
gt; and you are desperate for a solution
gt; and not afraid of iterate...
gt; in
gt; 3
gt; sum
gt; 12
gt; count
gt; 4
gt; avg_in
gt; 3
gt; option
gt; 3
gt; reset
gt; 1
gt; lock
gt; 1
gt; Name the cells as shown.
gt; Set iterate to 1.
gt; Create three option buttons,
gt; label them Reset, Set and Lock
gt; and link them to the cell lt;optiongt;.
gt; Into the lt;sumgt;, lt;countgt;, lt;avg_ingt;, lt;resetgt; and lt;lockgt; cell,
gt; enter these formulas respectively
gt; =IF(lock=1,sum,(sum in)*reset)
gt; =IF(lock=1,count,(count 1)*reset)
gt; =IF(reset=0,0,sum/count)
gt; =IF(option=1,0,1)
gt; =IF(option=3,1,0)
gt; Start by clicking the Reset button and
gt; entering a number into lt;ingt;.
gt; Click the Set button and verify that you got the desired results.
gt; Keep entering more numbers into lt;ingt; or stop and click the Lock button.
gt; If you do not Lock it, any other recalculation on the sheet
gt; will keep adding the last value of lt;ingt;.
gt; Always enter a new number into lt;ingt; before clicking Set.
gt;
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