I am trying to compare a randomly generated number by the previous randomly
generated number in the same cell. I would like to know if the new number is
higher or lower than the provious number. Once known i would like to add up
these occurances. ie out of 100 calculations 45 were higher and 55 were
lower. 45 and 55 should be shown in the next cells. Any suggestions would
be a great help.

Philip Morritt
University West England Bristol

I'd copy and paste the first column of data so that when you recalculate,
you don't lose your numbers.
Create another column of random numbers.
=if(a1B1,"Less",if(a1<b1,"More",Equal))

"Philip Morritt" <Philip wrote in message
...
I am trying to compare a randomly generated number by the previous randomly
generated number in the same cell. I would like to know if the new number
is
higher or lower than the provious number. Once known i would like to add
up
these occurances. ie out of 100 calculations 45 were higher and 55 were
lower. 45 and 55 should be shown in the next cells. Any suggestions
would
be a great help.

Philip Morritt
University West England Bristol

If the range of numbers is in A1:A100, use

=SUMPRODUCT(--(A1:A99<A2:A100))

for higher


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Philip Morritt" <Philip wrote in message
...
I am trying to compare a randomly generated number by the previous
randomly
generated number in the same cell. I would like to know if the new number
is
higher or lower than the provious number. Once known i would like to add
up
these occurances. ie out of 100 calculations 45 were higher and 55 were
lower. 45 and 55 should be shown in the next cells. Any suggestions
would
be a great help.

Philip Morritt
University West England Bristol

Thanks for that but unfortunatly the random number equation is copied which
produces a new number when pasting, it runs the calculation as if hitting F9.

"Barb Reinhardt" wrote:

I'd copy and paste the first column of data so that when you recalculate,
you don't lose your numbers.
Create another column of random numbers.
=if(a1B1,"Less",if(a1<b1,"More",Equal))

"Philip Morritt" <Philip wrote in message
...
I am trying to compare a randomly generated number by the previous randomly
generated number in the same cell. I would like to know if the new number
is
higher or lower than the provious number. Once known i would like to add
up
these occurances. ie out of 100 calculations 45 were higher and 55 were
lower. 45 and 55 should be shown in the next cells. Any suggestions
would
be a great help.

Philip Morritt
University West England Bristol

Philip Morritt wrote:

Thanks for that but unfortunatly the random number equation is copied which
produces a new number when pasting, it runs the calculation as if hitting F9.

You want to use Paste Special / # values. That will preserve the numeric
values of the original set of random numbers. Why do you want to do this?

Regards,
Martin Brown

"Barb Reinhardt" wrote:


I'd copy and paste the first column of data so that when you recalculate,
you don't lose your numbers.
Create another column of random numbers.
=if(a1B1,"Less",if(a1<b1,"More",Equal))

"Philip Morritt" <Philip wrote in message
...

I am trying to compare a randomly generated number by the previous randomly
generated number in the same cell. I would like to know if the new number
is
higher or lower than the provious number. Once known i would like to add
up
these occurances. ie out of 100 calculations 45 were higher and 55 were
lower. 45 and 55 should be shown in the next cells. Any suggestions
would
be a great help.

Philip Morritt
University West England Bristol

It's easy enough to compare and record changes made by your Random formula.
Problem is each time you update cells with change comparisons a new
Calculate event will trigger a new Random change.

You could store changes in a VBA variables or array and access when needed -

Right click sheet tab, view code and paste into the sheet module

Dim prevRnd As Double

Private Sub Worksheet_Calculate()
Dim newRnd As Double

newRnd = Range("A1").Value ' // change to address of RAND formula

If newRnd prevRnd Then
aRndCount(1) = aRndCount(1) + 1 ' more count
ElseIf newRnd < prevRnd Then
aRndCount(2) = aRndCount(2) + 1 ' less count
Else
aRndCount(3) = aRndCount(3) + 1 ' same count
End If
aRndCount(4) = aRndCount(4) + 1 ' total changes count
prevRnd = newRnd

End Sub

Insert a normal module for the following code

' top of normal module

Public aRndCount(1 To 4) As Long

Sub DumpRndCount()
' may trigger a calculation event & new Rand

' first select first cell of a row of four to dump values

ActiveCell.Resize(1, 4).Value = aRndCount

If MsgBox("Clear Rand History", vbYesNo) = vbYes Then
Erase aRndHistory
End If

End Sub

Sub RndCountMsg()
' does not trigger a calc event
Dim s As String
s = "More : " & vbTab & aRndCount(1) & vbCr
s = s & "Less : " & vbTab & aRndCount(2) & vbCr
s = s & "Same : " & vbTab & aRndCount(3) & vbCr
s = s & "Total : " & vbTab & aRndCount(4) & vbCr

MsgBox s

If MsgBox("Clear Rand History", vbYesNo) = vbYes Then
Erase aRndCount
End If
End Sub

Along similar lines could store in an array up to 64k actual rand values and
return the entire history to cells.

Regards,
Peter T

"Philip Morritt" <Philip wrote in message
...
I am trying to compare a randomly generated number by the previous
randomly
generated number in the same cell. I would like to know if the new number
is
higher or lower than the provious number. Once known i would like to add
up
these occurances. ie out of 100 calculations 45 were higher and 55 were
lower. 45 and 55 should be shown in the next cells. Any suggestions
would
be a great help.

Philip Morritt
University West England Bristol

You could try this

ToolsOptionsCalculation and check the Iteration box to stop circular
references

Adjust your randomising formula, in a manner like this

=IF(($B$1="")+(A10),A1,INT(RAND()*100+1))

Add my formula =SUMPRODUCT(--(A1:A99<A2:A100)) in another cell

then put some value in B1

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Bob Phillips" wrote in message
...
If the range of numbers is in A1:A100, use

=SUMPRODUCT(--(A1:A99<A2:A100))

for higher


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Philip Morritt" <Philip wrote in
message
...
I am trying to compare a randomly generated number by the previous
randomly
generated number in the same cell. I would like to know if the new
number
is
higher or lower than the provious number. Once known i would like to
add
up
these occurances. ie out of 100 calculations 45 were higher and 55 were
lower. 45 and 55 should be shown in the next cells. Any suggestions
would
be a great help.

Philip Morritt
University West England Bristol

Probably best to also use

=IF(($B$1="")+(E10),E1,SUMPRODUCT(--(A1:A99<A2:A100)))

where E1 is the cell of that formula.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Bob Phillips" wrote in message
...
You could try this

ToolsOptionsCalculation and check the Iteration box to stop circular
references

Adjust your randomising formula, in a manner like this

=IF(($B$1="")+(A10),A1,INT(RAND()*100+1))

Add my formula =SUMPRODUCT(--(A1:A99<A2:A100)) in another cell

then put some value in B1

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Bob Phillips" wrote in message
...
If the range of numbers is in A1:A100, use

=SUMPRODUCT(--(A1:A99<A2:A100))

for higher


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Philip Morritt" <Philip wrote in
message
...
I am trying to compare a randomly generated number by the previous
randomly
generated number in the same cell. I would like to know if the new
number
is
higher or lower than the provious number. Once known i would like to
add
up
these occurances. ie out of 100 calculations 45 were higher and 55
were
lower. 45 and 55 should be shown in the next cells. Any suggestions
would
be a great help.

Philip Morritt
University West England Bristol

Philip,

I really don't know the object of your exercise-comparing which way a
number falls. In principle, random number generation algorithm ensures
that there will, -on average-, be an even chance of getting either
event. If you populate A1:A100 & B1:B100 with random numbers, and you
repeat the exercise N times, you are likely to generate a distribution
where the number of times values in Column A exceed those in Column B
will flatten out. The higher the value of N, the greater the probabilty
being 0.5 each way. Any departure from this asymptotic value is purely
attributable to random error in the sampling process.

Just an observation.


--
Myles
------------------------------------------------------------------------
Myles's Profile: http://www.excelforum.com/member.php...o&userid=28746
View this thread: http://www.excelforum.com/showthread...hreadid=501462