Random function - odd error and not sure why

  • Thread starter Thread starter ghobbit
  • Start date Start date
G

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 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")
 
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.
 
Back
Top