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

InvictusSol

New Member
Joined
Jan 5, 2016
Messages
15
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.
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

mandukes

Forum Rules
Joined
May 25, 2013
Messages
90
Try this..

Code:
'[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

New Member
Joined
Jan 5, 2016
Messages
15
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

Forum Rules
Joined
May 25, 2013
Messages
90
Press alt+F11

Then Right click on Modules >Insert>Module.


9f6c1e7c351eed788349915a724770c0.png




Then paste your code.


8a5e9eb18b7719147f3af4cd9ff895f7.png




Run the macro by pressing the play button. :biggrin:
 
Last edited:

mandukes

Forum Rules
Joined
May 25, 2013
Messages
90

ADVERTISEMENT

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

InvictusSol

New Member
Joined
Jan 5, 2016
Messages
15
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

Forum Rules
Joined
May 25, 2013
Messages
90
' 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
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,802
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>
 

Watch MrExcel Video

Forum statistics

Threads
1,129,361
Messages
5,635,804
Members
416,884
Latest member
leeshjay

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top