=RAND produces decimals not whole numbers

[Guest]

I am a teacher making randomised equation sheets for young children in my
school, along with simultaneous answer sheets for teachers to speed up
marking, (plenty of educational websites already do this but I wanted to
customise my own using Excel).

No problem with setting out the equations, which I do by simply putting one
"randomised" cell above another and placing a dummy operation sign, such as
"x", beside the equation. For the simplest equations I use =RAND()*(1-9)+9 to
give cell values between 1 and 9. I'm new to this, so hope I'm right so far!

For the answers the cell references are copied to a second page in the same
worksheet, with a =SUM equation placed underneath. This where the problems
start. For instance 3 x 6 might, (and should, if I value my career!), appear
as 18, but has also come out as 20 and even 21!

I discovered the problem in the cell formatting. While Number Formatting
shows the correct whole number, (3 for example), General Formatting shows
that the number is actually 2.875260427 or 3.154202565. It's different each
time. So, although I am reading whole numbers on screen, Excel is busily
calculating numbers to 9 decimal places then rounding up to 18, 19, 20, or
21, depending on which it has randomly chosen.

I did a quick test typing in numbers manually and, as expected, each whole
number appeared in General Formatting as a whole number, so this is purely a
problem with random numbers which cannot be altered by specifying fewer
decimal places in Number Formatting.

Is there a way I can customise the cells, or =RAND, to reduce the number of
decimals in General Formatting from 9 to zero and just have
honest-to-goodness whole numbers producing whole answers?!

 

[Guest]

Instead of:

=RAND()*(1-9)+9

try:

=ROUND(RAND()*8,0)+1

 

[Roger Govier]

Hi

You could wrap the formula in an Int() expression
=INT(RAND()*(1-9)+9)

 

[Guest]

Perfect thank you!!

I've just made a simple equation using your formula and tested it out
several times successfully. Based on your equation I also experimented with
=ROUND(RAND()*98,0)+1 to produce numbers from 1-99, which also worked like a
dream.

 

[Guest]

Will have a go at this too. Anything extra I can learn about Excel is always
useful! Will reply later. Thank you.

 

[Dana DeLouis]

... Anything extra I can learn about Excel is always useful!

There is also an ATP function to give you another option:

=RANDBETWEEN(1,9)

 

[James Silverton]

Hello, Dana!
You wrote on Sun, 19 Nov 2006 11:55:16 -0500:

??>> ... Anything extra I can learn about Excel is always
??>> useful!

DD> There is also an ATP function to give you another option:

DD> =RANDBETWEEN(1,9)

I might add that RANDBETWEEN can be used for any reasonable
range of integers, eg.

RANDBETWEEN(1,999999)

if, for example, you want random integers in that large range.
I don't know the limits offhand, perhaps HELP will!


James Silverton
Potomac, Maryland

E-mail, with obvious alterations:
not.jim.silverton.at.comcast.not

 

[Bernard Liengme]

And you have learnt that formatting changes how a value is displayed NOT how
it is stored.

 

[Guest]

This isn't a good idea!

Although the formula

=ROUND(RAND()*8,0)+1

will indeed return a number between 1 and 9, the distribution won't be even.
This gives half as many 1s and 9s as the other numbers, try using that
formula 1000 times in a column and counting how many of each number there are
and you'll see what I mean. Better to use

=INT(RAND()*9+1)

 

[Tushar Mehta]

The proposed solution might be adequate for your work, but it does *not*
generate the numbers with equal probability. The first and the last are
only 1/2 as likely as the rest.

The correct way to generate with equal probability integers between a and b,
both inclusive, with a < b, is a+INT(RAND()*(b-a+1))
--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions