Saqib Husain

asked on

# VBA: Getting a random number

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?

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?

What is the range of values you are seeking? Maximum and minimum?

Kevin

Kevin

ASKER

10^ (+/-5)

Does this work?

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

Kevin

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

Kevin

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

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

ASKER

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?

I showed you how above. That's all VBA dude.

Kevin

Kevin

ASKER

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

pwr = Rnd()

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

num = Rnd()

num = Round(num, 4)

num = num * pwr

Convert to currency data type, which has four decimal places.

**Example:**`num = CCur(Rnd() * 10 ^ Int((Rnd * 10) - 5))`

ASKER

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.00001000 to 999900

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

so are you looking for something like this?

cdbl(rnd()*1000000)

sample result: 289562.463760376

cdbl(rnd()*1000000)

sample result: 289562.463760376

ASKER CERTIFIED SOLUTION

membership

Create an account to see this answer

Signing up is free. No credit card required.

@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

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.
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

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(intNumSignificantDi

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

ASKER

rspahitz

andrewssd3

I still have to test your solution

aikimark

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

sample result: 289562.463760376Nope 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

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.

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.

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.

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

Jim.

<<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.

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.

This will give you a four digit integer.

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).

`Int(Rnd() * 1000) `

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)`

**Note:**your Num variable should be a Double data type.
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.

Max - 999900

Min - 0.00001

Which I think is what you wanted.

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:

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.

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)`

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)
```

If it helps, here's what I used for testing:

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.

```
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
```

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.

ASKER

First solution that passes.

Thanks

Thanks

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"

It will also produce numbers from 1 to 999, which will leave you without those "4 significant digits"

ASKER

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

0.00001000 to 999900

which indicates that 0.00001000

**is**a member.
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.

But glad you got your working answer.

ASKER

No problem with getting things clarified.

Yes it is valid.

Yes it is valid.

ASKER

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.

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.

Kevin