=RAND produces decimals not whole numbers
 

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?!

=RAND produces decimals not whole numbers
 

Instead of:

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

try:

=ROUND(RAND()*8,0)+1
--
Gary's Student


"moosegate" wrote:

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?!

=RAND produces decimals not whole numbers
 

Hi

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

--
Regards

Roger Govier


"moosegate" wrote in message
...
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?!

=RAND produces decimals not whole numbers
 

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.


"Gary''s Student" wrote:

Instead of:

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

try:

=ROUND(RAND()*8,0)+1
--
Gary's Student


"moosegate" wrote:

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?!

=RAND produces decimals not whole numbers
 

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

"Roger Govier" wrote:

Hi

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

--
Regards

Roger Govier


"moosegate" wrote in message
...
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?!

=RAND produces decimals not whole numbers
 

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

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

=RANDBETWEEN(1,9)

--
Dana DeLouis
Windows XP & Office 2003


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

"Roger Govier" wrote:

Hi

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

--
Regards

Roger Govier


"moosegate" wrote in message
...
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?!

=RAND produces decimals not whole numbers
 

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

=RAND produces decimals not whole numbers
 

And you have learnt that formatting changes how a value is displayed NOT how
it is stored.
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"moosegate" wrote in message
...
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?!

=RAND produces decimals not whole numbers
 

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)


"Gary''s Student" wrote:

Instead of:

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

try:

=ROUND(RAND()*8,0)+1
--
Gary's Student


"moosegate" wrote:

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?!

=RAND produces decimals not whole numbers
 

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

In article ,
says...
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.


"Gary''s Student" wrote:

Instead of:

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

try:

=ROUND(RAND()*8,0)+1
--
Gary's Student


"moosegate" wrote:

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?!