Generating 365 Random numbers (0 to 5) that will sum to a specific value (1477)?

[dr3amxstar]

I'm a beginner in excel and would like to ask how do I generate 365 Random numbers (0 to 5) in Cell A1 to A365 that will sum to a specific value (1477)? And to be able to re-generate another set of random number when i hit F9? Appreciate the help! Randbetween doesnt seems to solve this problem.

[millz]

Try running this on a blank worksheet

Please Login or Register  to view this content.

[dr3amxstar]

Thank you so much for the prompt reply! It works perfectly. I would like to generate another 365 Random numbers (0 to 5) on C1 to C365 that will sum to 913. I tried to add on to the code but couldnt get the final sum of 913. Is there something wrong with my coding? Thank you so much once again!


Function rand(ByVal min As Double, Optional ByVal max As Double)
Dim r As Double
If max = 0 Then
max = min
min = 0
End If
r = Rnd
rand = (r * 1000000) - ((max - min) * Int((r * 1000000) / (max - min))) + min
End Function

Sub EF977634_Rand()
Dim i As Long
i = 1
Do
Range("A1:A365").Formula = "=randbetween(0,5)"
Range("B1").Formula = "=SUM(A1:A365)"
ActiveSheet.Calculate
i = i + 1
Loop While Range("B1").Value <> 1477 And i <= 100 'do 100 attempts see if we get 1477

With Range("A1:A365")
.Value = .Value
End With

If Range("B1").Value = 1477 Then Exit Sub

Do
i = rand(1, 365)
If Cells(i, 1).Value < 5 Then
Cells(i, 1).Value = Cells(i, 1).Value + 1
ActiveSheet.Calculate
End If
Loop While Range("B1").Value <> 1477 'find random number to increment until we get 1477

Dim n As Long
n = 1
Do
Range("C1:C365").Formula = "=randbetween(0,5)"
Range("D1").Formula = "=SUM(C1:C365)"
ActiveSheet.Calculate
n = n + 1
Loop While Range("D1").Value <> 913 And n <= 100 'do 100 attempts see if we get 913

With Range("C1:C365")
.Value = .Value
End With

If Range("D1").Value = 913 Then Exit Sub

Do
i = rand(1, 365)
If Cells(n, 1).Value < 5 Then
Cells(n, 1).Value = Cells(n, 1).Value + 1
ActiveSheet.Calculate
End If
Loop While Range("D1").Value <> 913 'find random number to increment until we get 913

End Sub

[millz]

You are welcome, but in the future please post code in

Please Login or Register  to view this content.

[dr3amxstar]

Noted about the coding! Thank you so much for your help!

[onlineriaz]

It is great thing,but how can we use it for a range of numbers, like if we want to have more than one numbers e.g. 55, 60, 49 and we need random data to be generated between 1-5 against these numbers in one go.

[millz]

Hi onlineriaz, welcome.

Please take some time to read the forum rules. Your post does not comply with the forum rules, in this case rule #2.

You should start your own thread instead.