RANDBETWEEN function returns #NAME?

  • Thread starter Thread starter Dave F
  • Start date Start date
D

Dave F

Using this formula,
=IF(K2="BER",randbetween(22,26),randbetween(35,37))

I get the #NAME? error.

Analysis toolpak is installed. Using a work pc with XL 03 and relying
on company IT department to solve this issue is not in the cards.

SO, how can this formula be rewritten using the RAND function?
Presumably some combination of RAND and INT but I'm not sure.

Thanks.

Dave
 
do other functions from the analysis toolpack work?
Networkdays() etc.
try reinstalling and reloading analysis toolpack?

=22+floor(rand()*4.9999999999999,1) is approximately the same as
randbetween(22,226)
 
I do not know exactly the purpose of RANDBETWEEN function but I guess it
returns and random number between the two boundaries. With RAND and INT, as
you guessed, you can build such function, for example:
=INT(22+RAND()*(26-22)) will return a random integer from 22, 23, 24 and 25.
note that the upper boundary (26) is NOT included in the possible numbers to
be returned, so change to =INT(22+RAND()*(27-22)) to include it.
 
There is a reported bug where even if Analysis toolpak is enabled it
occasionally fails to load. Sometimes closing Excel & reopening does the
trick, but otherwise it may be worth disabling then re-enabling the analysis
toolpak.

As far as your suggestion of RAND and INT is concerned, try
=IF(K2="BER",INT(22+5*RAND()),INT(35+3*RAND()))
 
David, yes I happened to do this, and it seemed to fix the problem.

Thanks, also, for your alternative formula.

Dave
 
Back
Top