Static Randomisation Probability
Results 1 to 2 of 2

Thread: Static Randomisation Probability
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Oct 2014
    Posts
    54
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Static Randomisation Probability

    Hi, I have the following formula working out a random Yes / No based on some probability.

    In B23 I have the following code (it sits in a table)

    Code:
    =IF(A23="","",INDEX($A$2:$A$3,COUNTIF($C$2:$C$3,"<="&RAND())+1))
    However as more data gets added below A23, it recalculates.

    What I'm looking for is a static calculation, so that when A23 is populated B23 calcs once. Then when A24 is populated B24 calculates, but B23 remains static and does not change.

    I've got the following vba code in a module and B23 has =StaticRand5. It works for this but how do I get it to do the
    Code:
    =IF(A23="","",INDEX($A$2:$A$3,COUNTIF($C$2:$C$3,"<="&RAND())+1))

    Code:
    Function StaticRand5() As Double
    StaticRand5 = Int((20 - 1 + 1) * Rnd() + 1)
    End Function
    any help would be appreciated. Thanks S

  2. #2
    Board Regular
    Join Date
    Oct 2014
    Posts
    54
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Static Randomisation Probability

    Ok, so I know I need to create a Function to run my calculation, however I'm trying the following code and get "0" value?

    Code:
    Function StaticRand() As Double
    StaticRand = Evaluate("INDEX(A2:A3,COUNTIF(C2:C3," <= "&RAND())+1)")
    End Function
    Any help would be appreciated.

    Thanks S
    Last edited by Steven101; Mar 29th, 2018 at 08:46 AM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •