Random numbers / percentages between numbers

farmertml

Board Regular
Joined
Jun 16, 2005
Messages
62
Hi,

I'm using a random number generator in excel to create values between 17.0 and 21.0 to one decimal point.

Currently using =RANDBETWEEN(1700,2100)/100 to achieve this. (It will give me 18.3 / 19.4 etc etc).

I'm then using this to create 100 random numbers between 17 and 21. What I want to be able to do is out of these 100 random numbers to have 5% (5 numbers) to go down to 16.0 but randomly.

I dont want it to be a formula =RANDBETWEEN(1600,2100)/100 because I only want 5% to be less than 17.0.

Any help would be greatly appreciated.

Regards,
Paul
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Maybe:

Code:
Sub test()

Dim lngLowNum As Long
Dim lngRowNum As Long
Dim dblRandNum As Double
Dim myCount As Long

Randomize

lngLowNum = 160

For lngRowNum = 1 To 100

dblRandNum = Int((210 - lngLowNum + 1) * Rnd + lngLowNum) / 10

Cells(lngRowNum, 1) = dblRandNum

If dblRandNum < 17 Then

    myCount = myCount + 1
    If myCount = 5 Then
        lngLowNum = 170
    End If
    
End If

Next lngRowNum

End Sub

It will generate numbers in cells A1:A100.

Dom
 
Upvote 0
Here is a possible solution. Assuming your randbetween function is in column A, in column B, insert a new randbetween(1,100). Highlight the column B selections and open conditional format. Conditionally format the bottom 5% (Select 10% and amend in the next step of the wizard to 5) to highlight those having the bottom 5 values. Will this accomplish what you want for your selection process?

Alan
 
Upvote 0
Why not just use =RANDBETWEEN(1700,2100)/100 for 95 cells and =RANDBETWEEN(1600,1699)/100 for the other 5?
 
Upvote 0
ANother way, in J1:J5 add

=RANDBETWEEN(1,5)

then use

=IF(ISNUMBER(MATCH(ROW(),$J$1:$J$5,0)),RANDBETWEEN(1600,1699)/100,RANDBETWEEN(1700,2100)/100)
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

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
Back
Top