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

[InvictusSol]

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.

 

[mandukes]

Try this..

'[URL="https://www.mrexcel.com/forum/members/invictussol.html"]InvictusSol[/URL][COLOR=#574123] 
[/COLOR]'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

 

[InvictusSol]

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.

 

[mandukes]

Press alt+F11

Then Right click on Modules >Insert>Module.

Then paste your code.

Run the macro by pressing the play button.

 

[mandukes]

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

 

[InvictusSol]

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

 

[mandukes]

' There you go mate.

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

 

[Eric W]

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.
<a6 100,"yes","no")
</a6>