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
    Forum Rules
    Join Date
    May 2013
    Posts
    89
    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
    Forum Rules
    Join Date
    May 2013
    Posts
    89
    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
    Forum Rules
    Join Date
    May 2013
    Posts
    89
    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
    Forum Rules
    Join Date
    May 2013
    Posts
    89
    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,919
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    2 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

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
  •