Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
=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?! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
=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?! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
=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?! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
=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?! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
=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?! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
=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?! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
=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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
=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?! |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
=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?! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is this possible ??? | Excel Discussion (Misc queries) | |||
How do I merge 2 cells to 1 when contents are numbers AND retain decimals | Excel Discussion (Misc queries) | |||
Help! How do you get excel to find the x(changes daily, marked in a cell from another formula) highest numbers in a group of numbers and sum them up? | Excel Worksheet Functions | |||
How to convert numbers with apostrophe's (ex. 219'2) to decimals? | Excel Worksheet Functions | |||
Checking Winning Numbers in the Lottery. | Excel Discussion (Misc queries) |