Solved

VBA: Getting a random number

Posted on 2013-05-25
29
470 Views
Last Modified: 2013-05-28
I am trying to get a random number with 4 significant figures using this formula

num = Round(Rnd(), 4) * 10 ^ Int((Rnd * 10) - 5)

Although the components work out nicely the final result comes up with lesser significant figures. How can I get only 4 significant figures in VBA?
0
Comment
Question by:Saqib Husain, Syed
  • 9
  • 5
  • 5
  • +3
29 Comments
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 39196995
num = Round(Rnd() * 10 ^ Int((Rnd * 10) - 5), 4)

Kevin
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 39196997
What is the range of values you are seeking? Maximum and minimum?

Kevin
0
 
LVL 43

Author Comment

by:Saqib Husain, Syed
ID: 39197000
10^ (+/-5)
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 39197012
Does this work?

Round(Rnd() * 10 ^ Int(Rnd() * 10 - 5), 4)

Kevin
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 39197022
Something weird is going on. There appears to be a bug in the VBA interpreter.

This:

Round(Rnd() * 10000, 4)

does not round to four places. It doesn't seem to round at all.

This works:

Dim Value As Double
Value = Rnd() * 10000
Value = Round(Value, 4)

Kevin
0
 
LVL 43

Author Comment

by:Saqib Husain, Syed
ID: 39197051
Currently I have applied the same formula to an excel cell and used the value of the cell. But my question still stands....how to get it directly through VBA?
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 39197080
I showed you how above. That's all VBA dude.

Kevin
0
 
LVL 43

Author Comment

by:Saqib Husain, Syed
ID: 39197269
This does not give me the desired results

pwr = Rnd()
pwr = 10 ^ Int((pwr * 10) - 5)
num = Rnd()
num = Round(num, 4)
num = num * pwr
0
 
LVL 45

Expert Comment

by:aikimark
ID: 39200183
Convert to currency data type, which has four decimal places.
Example:
num = CCur(Rnd() * 10 ^ Int((Rnd * 10) - 5))

Open in new window

0
 
LVL 43

Author Comment

by:Saqib Husain, Syed
ID: 39200221
aikimark, I am looking for numbers in the range

0.00001000 to 999900

and I do not think the currency will do this as 0.00001001 is a possibility.
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 39200320
so are you looking for something like this?

cdbl(rnd()*1000000)

sample result:  289562.463760376
0
 
LVL 17

Accepted Solution

by:
andrewssd3 earned 500 total points
ID: 39200459
Is this what you want? - just a bit more complication getting the original number, so it gets a random number between 1 and 9999 then multiplies it by 10^2 through 10^-5:
Dim pwr As Double
Dim num As Double

pwr = Rnd()
pwr = 10 ^ Int(pwr * 8 - 5)

num = Int(Rnd() * 9999 + 1)
num = num * pwr

Debug.Print num

Open in new window

0
 
LVL 45

Expert Comment

by:aikimark
ID: 39201175
@ssaqibh

You stated that you wanted a "number with 4 significant figures".  That generally means that you want a floating point number with a precision of four digits to the right of the decimal point.

Your two recent examples
0.00001000 and 0.00001001 go beyond your stated precision.  If displaying such values with a precision of four decimal places, both of these would be zero.
0
 
LVL 57
ID: 39201208
Not sure what Excel has built-in, but in Access VBA, I wrote the code below to round to a significant number of digits.  Probably better ways of doing this, but it did achieve the correct results.

Just feed it a random number and I believe it will give you what you want.

Jim.

Public Function RoundSignificant(varValue As Variant, intNumSignificantDigits As Integer) As String

    Dim strPrefix As String
   
    Dim dblFactor As Double
    Dim dblABSofValue As Double
    Dim strFormatedValue As String
   
    Dim lngPos As Long
    Dim bolDecimalPoint As Boolean
    Dim strChr As String
   
    Dim lngNumOfDigits As Long
       
   
    ' Check for a prefix ('>' or '<').  If there is one,
    ' strip it off for now.
    If left(varValue, 1) = ">" Or left(varValue, 1) < "<" Then
      strPrefix = left(varValue, 1)
      varValue = Val(Mid(varValue, 2))
    Else
      strPrefix = ""
    End If
   
    ' Check for null
    varValue = Nz(varValue, 0)

    ' If value of zero, return "N/A"
    If varValue = 0 Then
      RoundSignificant = "N/A"
    Else
     
      ' Get the factor
      dblFactor = 10 ^ Int(Log(Abs(varValue)) / Log(10) - intNumSignificantDigits + 1)
     
      ' Based on the factor, get an absolute value that's rounded.
      If dblFactor = 1 Then
         dblABSofValue = CLng((Abs(varValue) / dblFactor))
      ElseIf dblFactor > 1 Then
        dblABSofValue = CLng((Abs(varValue) / dblFactor) + (0.5 / dblFactor))
      Else
        dblABSofValue = CLng((Abs(varValue) / dblFactor) + (0.5 * dblFactor))
      End If
      dblABSofValue = dblABSofValue * dblFactor
      ' Format the value as a string.
      strFormatedValue = Format((IIf(varValue >= 0, 1, -1) * dblABSofValue), "#0.00000000000000000000")
     
      ' Do we have a decimal point?
      If InStr(strFormatedValue, ".") > 0 Then
        ' If so, chop off all zeros on the right
        While right(strFormatedValue, 1) = "0"
            strFormatedValue = left(strFormatedValue, Len(strFormatedValue) - 1)
        Wend
      End If
     
      ' Scan for the number of digits in the string
      For lngPos = 1 To Len(strFormatedValue)
        strChr = Mid(strFormatedValue, lngPos, 1)
           
        If strChr >= "0" And strChr <= "9" Then
          lngNumOfDigits = lngNumOfDigits + 1
        End If
       
        If strChr = "." Then
          bolDecimalPoint = True
        End If
      Next
     
      ' Is the number of digits found less then the significance required?
      If lngNumOfDigits < intNumSignificantDigits Then
        ' If so and we have decimal point, add some zeros
        If bolDecimalPoint = True Then
          strFormatedValue = strFormatedValue & String(intNumSignificantDigits - lngNumOfDigits, "0")
        End If
      End If
         
      ' Do we have anything to the right of the decimal?
      ' If not, remove the decimal point
      If right(strFormatedValue, 1) = "." Then
        strFormatedValue = left(strFormatedValue, Len(strFormatedValue) - 1)
      End If
     
      ' Add the prefix back in if we have one
      If strPrefix <> "" Then
        RoundSignificant = strPrefix & strFormatedValue
      Else
        RoundSignificant = strFormatedValue
      End If

    End If

End Function
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 43

Author Comment

by:Saqib Husain, Syed
ID: 39201256
rspahitz
sample result:  289562.463760376
Nope Only 4 significant figures



andrewssd3
I still have to test your solution

aikimark
You stated that you wanted a "number with 4 significant figures".  That generally means that you want a floating point number with a precision of four digits to the right of the decimal point.
examples of 4 significant figures are
1234000000
1234000
12340
123.4
12.34
1.234
0.1234
0.001234
0.000001234



 JDettman
I am too scared to try your solution unless you confirm that it meets my requirements as per the list ending four lines above
0
 
LVL 57
ID: 39201262
FYI, just found the formula for significant rounding:

round(10-n·x)·10n, where n = floor(log10 x) + 1 - p

x being a positive number to a precision of p significant digits.  I believe this will work in Excel.  For negatives use the abs() value.

Jim.
0
 
LVL 57
ID: 39201269
oops, no super scripts.  That's the round of 10 raised to the negative n times X times 10 rasied to n.

 Not sure if that will work or not, but you can try it.

Jim.
0
 
LVL 57
ID: 39201280
<<I am too scared to try your solution unless you confirm that it meets my requirements as per the list ending four lines above >>

 Just cut and paste into a module and then call it from a cell, but the formula I just posted is a lot more conscise for Excel.

Jim.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 39201575
This will give you a four digit integer.
Int(Rnd() * 1000) 

Open in new window


It is up to you to multiply it by some power of 10 that will move the decimal place to the right or left.  In your example, you wanted the power raised (decimal moved) from +2 (right) to -8 (left).
Num = Int(Rnd() * 1000) * 10^(Int(Rnd() *11)-8)

Open in new window


Note: your Num variable should be a Double data type.
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 39201656
Hi - I have just tested my solution in 100,000 iterations, and it gives

Max - 999900
Min - 0.00001

Which I think is what you wanted.
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 39201682
So you want 4 digits, but randomly placed within powers of 10?
Int(Rnd()*10000) will produce a number from 0 to 9999 (although some times it may round to 10000 so you may want a separate check for that).

then just multiple or divide by powers of 10.

So using your original example,

num= Int(Rnd()*10000) * 10 ^ Int((Rnd * 10) - 5)

However, sometimes you might get random numbers like 0123 so does that count as only 3 significant digits?  If so you really want a range of 1000-9999 so here:
num= Int(Rnd()*9000+1000) * 10 ^ Int((Rnd * 16) - 8)

Open in new window

Sample data from the above:

 0.004129
 93110
 0.0009643
 67.09
 0.0005886
 62970000
 285400000
 140.4
 3.291
 311.7

I just noticed that sometimes you will get less than 4 digits...that's because after multiplying by 9000, the right-most digit might be zero.  I guess you can prevent that by ensuring that your resulting number after adding 1000 is not divisible by 10.  the easiest way to handle that is to simply generate a new number since 90% of the time you'll be okay.

Do
tempNum=Int(Rnd()*9000+1000)
Loop Until (tempNum Mod 10)<>0
num= tempNum * 10 ^ Int((Rnd * 16) - 8)

Open in new window

0
 
LVL 22

Expert Comment

by:rspahitz
ID: 39201769
If it helps, here's what I used for testing:

    Dim num As Double
    Dim origNum As Double
    Dim tempNum As Double
    Dim iCntr As Integer
    
    For iCntr = 1 To 100
        Do
            origNum = Rnd
            tempNum = Int(origNum * 9000 + 1000)
            'If tempNum Mod 10 = 0 Then
            '    Debug.Print ;
            'End If
        Loop Until (tempNum Mod 10) <> 0
        num = tempNum * 10 ^ Int((Rnd * 16) - 8)
        Debug.Print origNum; tempNum; num 'Int(num * 9000 + 1000) * 10 ^ Int((Rnd * 16) - 8)
    Next

Open in new window


The Immediate window will show the original number, the number before the power (but after multiplying by 9000 and adding 1000, then the final number.

Sample results:

 0.775829255580902  7982  798200000
 0.893206655979156  9038  903.8
 0.255511462688446  3299  3.299
 0.769428551197052  7924  792400000
 0.173246800899506  2559  25.59
 0.314691126346588  3832  0.003832
 0.651410758495331  6862  0.00006862

So this will produce a 4-digit number where neither the leading nor trailing digit is zero, since apparently that is not considered significant.  i.e. 1230 is no good because it only has 3 significant digits.  Likewise for 0.01230 but okay for 1001.
0
 
LVL 43

Author Closing Comment

by:Saqib Husain, Syed
ID: 39201919
First solution that passes.

Thanks
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 39202086
Good idea, but it will fail on numbers that are powers of 10, like 9370, which might appear as 9370000 or .00937
It will also produce numbers from 1 to 999, which will leave you without those "4 significant digits"
0
 
LVL 43

Author Comment

by:Saqib Husain, Syed
ID: 39202109
rspahitz, those numbers are part of the range of 4 significant figures. In one of my clarifications I specified the range

0.00001000 to 999900

which indicates that 0.00001000 is a member.
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 39202116
not to argue, but then that means that 0.00001 is valid, since it's the same as 0.00001000 (but formatted differently.)
But glad you got your working answer.
0
 
LVL 43

Author Comment

by:Saqib Husain, Syed
ID: 39202143
No problem with getting things clarified.

Yes it is valid.
0
 
LVL 43

Author Comment

by:Saqib Husain, Syed
ID: 39202154
My original question says

How can I get only 4 significant figures in VBA?


Which probably should have been

How can I get not more than 4 significant figures in VBA?

to eliminate the confusion.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now