(RAND?) function to give "YES" or "NO" response
(RAND?) function to give "YES" or "NO" response
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: (RAND?) function to give "YES" or "NO" response

  1. #1
    New Member
    Join Date
    Jan 2016
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default (RAND?) function to give "YES" or "NO" response

     
    Hi Guys,

    This will be fairly easy no doubt, but I've already wasted an hour+ trying to come up with a solution so any help is of course appreciated.

    I have a column of numbers (let's say A2:A20) which are populated randomly by integers between 1 and 100. These numbers in actuality are the %age chance of an event occurring.


    I'd like to populate B2:B20 with either a YES or NO, based on the %age chance represented in A.


    For instance, "82" is in A3 and I'd like B3 to have an 82% chance or 82/100 chance of returning "YES" and an 18% chance of returning "NO".
    Conversely, "10" is in A4 and I'd like B4 to have an 10% chance or 10/100 chance of returning "YES" and an 90% chance of returning "NO".


    Again, any assistance greatly appreciated. Thanks.

  2. #2
    Board Regular
    Join Date
    May 2013
    Posts
    78
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: (RAND?) function to give "YES" or "NO" response

    Try this..

    Code:
    'InvictusSol 
    'Tested on Excel 2016 
    'Platform - Windows 10
    
    Sub YN()
    
    
    Dim Lastrow As Long
    Dim Pval As Integer
    Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    
    
    For I = 2 To Lastrow
    If Range("A" & I) <> "" Then
    Pval = Range("A" & I).Value * 100
    Z = (100 - Pval)
    Range("B" & I) = (100 - Pval) & " Yes " & Pval & " No"
    End If
    Next I
    End Sub

  3. #3
    New Member
    Join Date
    Jan 2016
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: (RAND?) function to give "YES" or "NO" response

    Thank you. Excuse my ignorance, but is that a Macro I need to record/input?

    If yes, all I'm experienced in Macro-wise is recording manual moves then editing the ensuing code to suit etc. I may need walking through.


    FWIW, I tried to head down the INDEX path and RANDBETWEEN path trying to reference a 100x100 cell table where there were 99xYES & 1xNO, 98xYES & 2xNO etc but am having trouble referencing each specific row I'd need - so no luck there.


    I'll try my best to make sense of what you've provided. Thanks again.

  4. #4
    Board Regular
    Join Date
    May 2013
    Posts
    78
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: (RAND?) function to give "YES" or "NO" response

    Press alt+F11

    Then Right click on Modules >Insert>Module.






    Then paste your code.






    Run the macro by pressing the play button.
    Last edited by mandukes; Nov 26th, 2017 at 08:07 AM.

  5. #5
    Board Regular
    Join Date
    May 2013
    Posts
    78
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: (RAND?) function to give "YES" or "NO" response

    This macro would only give "Yes" "No" Response based on values represented in A.

  6. #6
    New Member
    Join Date
    Jan 2016
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: (RAND?) function to give "YES" or "NO" response

    I've either messed something up or something has been lost in translation.

    I managed to work out the macro, but the returned values are text and read "28 Yes 72 No" or "37 Yes 63 No" etc.


    What I was hoping for is for my randomly sourced values in $A$6:$A$10 (for ease of explanation A6 = 10, A7 = 30, A8 = 50, A9 = 70, A10 = 90), values to be returned in $B$6:$B$10 would be:

    - B6 have a 10% chance of returning YES, 90% chance of returning NO
    - B7 have a 30% chance of returning YES, 70% chance of returning NO
    - B8 have a 50% chance of returning YES, 50% chance of returning NO
    - B9 have a 70% chance of returning YES, 30% chance of returning NO
    - B10 have a 90% chance of returning YES, 10% chance of returning NO

  7. #7
    Board Regular
    Join Date
    May 2013
    Posts
    78
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: (RAND?) function to give "YES" or "NO" response

    ' There you go mate.

    Code:
    Sub YN()
    Dim Lastrow As Long
    Dim Pval As Integer
    Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    For I = 2 To Lastrow
    If Range("A" & I) <> "" Then
    Pval = Range("A" & I).Value * 100
    Z = (100 - Pval)
    Range("B" & I) = Pval & "% Chance of returning  YES," & (100 - Pval) & "%  chance of returning NO"
    End If
    Next I
    End Sub

  8. #8
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    6,119
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)

    Default Re: (RAND?) function to give "YES" or "NO" response

      
    Perhaps you just need a formula?

    B6: =IF(RAND() < A6/100,"Yes","No")

    Put that in and drag down. You can recalculate the list by pressing F9, and you'll see that the Yes/No values change in rough agreement with the values in A.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

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
  •  

 

 
DMCA.com