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

#### InvictusSol

##### New Member
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
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
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
Press alt+F11

Then Right click on Modules >Insert>Module.

Run the macro by pressing the play button.

Last edited:

#### mandukes

##### Forum Rules

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

#### InvictusSol

##### New Member
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
' 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
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>

Replies
6
Views
3K
Replies
1
Views
405
Replies
9
Views
80
Replies
9
Views
134
Replies
0
Views
1K

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.

### Which adblocker are you using?

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

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