How do I do this, can it be done?

D

Dark Horse

I recently posted a question which received not a single response - so I do
not know if what I want to do is possible, or if I have asked the question
in the correct place.

Perhaps someone will be kind enough to let me know?

In Excel, is there any way of performing two functions in the same cell?

I'm trying to stop a list of columns extending into infinity by finding
another way to calculate a running average.

In BASIC there would have been a way, and I just wonder if there is a way in
math spreadsheets?
I could have added A (the latest entry) to B, (the running total) and at the
same time added 1 to count (which was originally set to 1).

I could then just repeat this by adding any new data to A, which will add it
to B (and add 1 to count at the same time) and give me a running total which
I can divide by count.
Unfortunately my knowledge of Excel is not as good as my knowledge of BASIC
was, so I don't know if it can be done?
Thanks in anticipation, sorry if it is so blindingly easy that I should have
seen how to do it myself.
Dave
 
P

Pete_UK

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
 
D

Dark Horse

Pete_UK said:
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
Click to expand...
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
 
H

Harlan Grove

Dark Horse wrote.......

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.
 
H

Herbert Seidenberg

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.
 
D

Dark Horse

Herbert Seidenberg said:
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.
Click to expand...
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
 
Top