Random function - odd error and not sure why

[ghobbit]

Hi

I have query regarding random numbers

I have a column of numbers all starting with the prefix VC and I wan
to choose a random number from them. I have this function to do this

=TEXT(RANDBETWEEN(5347,6598),"VC0000")

and this does exactly what I expect it to - produce a random numbe
between 5347 and 6598 and puts the prefix VC on the front.

I also have a column of numbers all with the prefix AH however when
try exactly the same thing but simply changing the "VC0000" wit
"AH0000" to try and get the prefix AH I get a #value error.

I can use a value "XC0000" on a column of numbers with prefix XC an
that works but "XY0000" wont and gives the same #value error and I'
not quite sure why.

Anything obvious I'm doing wrong?

regards

Stev

 

[Frank Kabel]

Hi
try:
=TEXT(RANDBETWEEN(5347,6598),"""AH""0000")

 

[ghobbit]

That solved it - many thanks for your quick reply

stev

 

[JE McGimpsey]

Frank gave you a workaround, but the answer to your question is that
yes, you're using reserved characters in you formatting strings. H is
used for Hours, Y for years, etc. If you don't want to use double quotes
you can also use the \ switch:


=TEXT(RANDBETWEEN(...),"A\H0000")

 

[Myrna Larson]

So you'll undertand what the issue is "H" is a "reserved" character in a
number format. It indicates that the number represents a time, and to display
the hour associated with that time.