RANDBETWEEN function to work in Excel?

[Guest]

I am trying to use the RANDOM function in Excel to generate a response that
includes both negative and positive numbers. However, I cannot get the
RANDBETWEEN function to work in Excel. Excel sees this as text and not a
function. What do I need to do to enable this function?

 

[Arvi Laanemets]

Hi

You must have Analysis Toolpack Ad-In installed and activated.


Arvi Laanemets

 

[Peo Sjoblom]

A workaround might be to use RAND

=RANDBETWEEN(-10,10)

the equivalent would be

=INT(RAND()*(10-(-10))-10)

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Leo Heuser]

Hi Peo

I believe you mean:

=INT(RAND()*(10-(-11))-10)
or
=INT(RAND()*21-10)

to be able to get the number 10

Leo

 

[Guest]

Both alternative functions work.

Is it possible to have the random numbers generated to have a normal
distribution(bell shaped curve) centered on zero?

 

[Tushar Mehta]

Use INT and RAND. To generate integer random numbers between a and b,
both inclusive, where a < b, use =INT(RAND()*(b-a+1))+a

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Multi-disciplinary business expertise
+ Technology skills
= Optimal solution to your business problem
Recipient Microsoft MVP award 2000-2004

 

[Michael R Middleton]

M. Cade Smith -

... Is it possible to have the random numbers generated to have a normal
distribution(bell shaped curve) centered on zero? <

For normally distributed random numbers with mean 0 and standard deviation
5, use

=NORMINV(RAND(),0,5)

- Mike

www.mikemiddleton.com

 

[Myrna Larson]

Tools/Data Analysis/Random Number Generation includes routines to generate a
list of normally distributed random numbers. If you want a formula, here's
some VBA code. The desired mean and SD and optional and default to 0 and 1.


Function RandomNormal(Optional Mean As Double = 0, _
Optional SD As Double = 1) As Double
'Optimized Box-Muller, from Numerical Recipes
'faster: no Sin, Cos, just 1 Sqr, 1 Log
Static HaveX1 As Boolean
Dim V1 As Double
Dim V2 As Double
Dim S As Double
Dim S1 As Double
Static X1 As Double
Dim X2 As Double

If HaveX1 = False Then
Do
V1 = Rnd() * 2 - 1
V2 = Rnd() * 2 - 1
S = V1 * V1 + V2 * V2
Loop While S >= 1# Or S = 0#

S1 = Sqr(-2 * Log(S) / S)
X1 = V1 * S1
X2 = V2 * S1

HaveX1 = True
RandomNormal = X2 * SD + Mean
Else
HaveX1 = False
RandomNormal = X1 * SD + Mean
End If
End Function 'RandomNormal