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

Thanks:  0
Likes:  0

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

1. ## (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. ## 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. ## 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. ## 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.

5. ## Re: (RAND?) function to give "YES" or "NO" response

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

6. ## 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. ## 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. ## 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.

## User Tag List

#### Posting Permissions

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