Running Totals

[Pickup]

Hi,

I'm working through a college project using Excel. One of the examples is
to generate a random event (toss of a coin) and then record the number of
outcomes ie how many times heads comes up & how many times tails comes up.

I can't see a way to record the running totals - there's a similar example
given in the notes but i think it might have been done in Lotus 1-2-3

IF(B2="Yes",b3=b3+1, b4=b4+1)

I've tried various permutations of IF, COUNTIF.

Thanks for looking - any suggestions gratefully received.

 

[Sean Timmons]

so, do you have a list of Yes and No's? If so, depending on direction..

If going from B2 - IV2, then...

B3 = countif(2:2,"Yes")
B4 = countif(2:2,"No")

would do that...

If you want a running total, then on:

B3, =if(B2="Yes",1,0)
B4 =if(B2="No",1,0)

then, in each subsequent column..

C3, =if(C2="Yes",B3+1,B3)
C4, =if(C2="Yes",B4+1,B4)

 

[Sean Timmons]

Oops, on that last formula should be

C4, =if(C2="No",B4+1,B4)

 

[Pickup]

Sean, Many thanks for the reply.

Sorry my omission - the question specifies that the results of each throw
should appear in a single cell (the throws are simulated by switching auto
calc off and then using F9 to generate a new number) with running totals
being kept for the count of heads & tails.

I think you mean B2 in your examples rather than C2 .. IF(C2="Yes"... ??

I tried something like this already unfortunately when the answer's no C3
would reset to 0. I also tried

C3, =IF(B2="Yes",B3=B3+1,B3=B3)
C4, =IF(B2="No",B4=B4+1,B4=B4)

and

C3, =IF(B2="Yes",B3+1,B3)
C4, =IF(B2="No",B4+1,B4)

but this doesn't appear to put a value into B3 or B4 - it just displays a
value in C3 (presumably because it's evaulating B3=B3+1)

Thanks again

Fraser

PS I've had problems in getting Excel to place a value in a cell referenced
in a formula before and used Macros to get round it but for this exercise we
cannot use Macros.

 

[David Biddulph]

Well, you've failed to quote any of the content of the message to which you
are replying, so it's a bit difficult for us to advise on the specifics, but
the main point that you've got wrong is that you seem to be expecting a
formula to "push" a value into another cell.
A formula doesn't do that; it writes the new value into the cell where the
formula is sitting.

 

[Pickup]

Hi David,

Thanks for the reply - I guess I'm in the habit of deleting extraneous info
and since it was listed in the previous posts thought it was superflous - I
take your point that it's easier for someone new to look at the most recent
post rather than go to the trouble of reading the first post.

I'm trying to keep a running total so yes I guess I am trying to push a
value into a cell. I know that other spreadsheet packages can do this but
I've never been able to find anything that states categorically Excel won't -
eternal optimist that I am I keep hoping this means that it can ;-)

Regards

Fraser