Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2000, RAND and IF functions do not work together for me
I am using Excel 2000, and I cannot get RAND and IF functions to work
together properly. For example, B2 is =RAND()*(6-1)+1 to generate a random number between 1 and 6. B3 is =IF(B2=3,"Yes") to return the value Yes if B2 is 3. But B3 always returns "FALSE" instead of any other value, even if B2 is 3. -- -Jesse CH |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2000, RAND and IF functions do not work together for me
Jesse,
It doesn't, it displays Yes when b2 = 3 so there is something wrong with the way you are seeing B2. As a start try this. Delete the formula from B2, format the cell as general and enter the number 3. Does B3 change to yes? If it does then out your formula back in B2 and it should work. Mike "Jesse CH" wrote: I am using Excel 2000, and I cannot get RAND and IF functions to work together properly. For example, B2 is =RAND()*(6-1)+1 to generate a random number between 1 and 6. B3 is =IF(B2=3,"Yes") to return the value Yes if B2 is 3. But B3 always returns "FALSE" instead of any other value, even if B2 is 3. -- -Jesse CH |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2000, RAND and IF functions do not work together for me
Jesse,
Another thought, there are literally tens of millions of random numbers between 1 and 6 so you have a better chance of winning the lottery than hitting exactly 3 and getting a YES in B3. Mike Mike "Mike H" wrote: Jesse, It doesn't, it displays Yes when b2 = 3 so there is something wrong with the way you are seeing B2. As a start try this. Delete the formula from B2, format the cell as general and enter the number 3. Does B3 change to yes? If it does then out your formula back in B2 and it should work. Mike "Jesse CH" wrote: I am using Excel 2000, and I cannot get RAND and IF functions to work together properly. For example, B2 is =RAND()*(6-1)+1 to generate a random number between 1 and 6. B3 is =IF(B2=3,"Yes") to return the value Yes if B2 is 3. But B3 always returns "FALSE" instead of any other value, even if B2 is 3. -- -Jesse CH |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2000, RAND and IF functions do not work together for me
When the cell B2 is reformatted to general and the number 3 is entered, I do
indeed see Yes. However, when I put the RAND function back in to B2, it still returns as FALSE. I have formatted the cell as Number with 0 decimal places, does that matter? I've also tried formatting individual and all cells as general and number. -- -Jesse CH "Mike H" wrote: Jesse, It doesn't, it displays Yes when b2 = 3 so there is something wrong with the way you are seeing B2. As a start try this. Delete the formula from B2, format the cell as general and enter the number 3. Does B3 change to yes? If it does then out your formula back in B2 and it should work. Mike -- -Jesse CH |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2000, RAND and IF functions do not work together for me
Jesse,
See my other reply, you never will, there are just too many numbers between 1 and 6 for it to be likely you will ever hit any integer let alone the one you want 3. Mike "Jesse CH" wrote: When the cell B2 is reformatted to general and the number 3 is entered, I do indeed see Yes. However, when I put the RAND function back in to B2, it still returns as FALSE. I have formatted the cell as Number with 0 decimal places, does that matter? I've also tried formatting individual and all cells as general and number. -- -Jesse CH "Mike H" wrote: Jesse, It doesn't, it displays Yes when b2 = 3 so there is something wrong with the way you are seeing B2. As a start try this. Delete the formula from B2, format the cell as general and enter the number 3. Does B3 change to yes? If it does then out your formula back in B2 and it should work. Mike -- -Jesse CH |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2000, RAND and IF functions do not work together for me
Jesse,
I missed one of your points. Formatting with zero decimal places has no effect except to alter the way you see the number, the underlying number is un-changed. If you really want to see a 3 change your formula to this and tap F9 a few times. Using the Int function reduces it to 5 options. =INT(RAND()*(6-1)+1) Mike "Jesse CH" wrote: When the cell B2 is reformatted to general and the number 3 is entered, I do indeed see Yes. However, when I put the RAND function back in to B2, it still returns as FALSE. I have formatted the cell as Number with 0 decimal places, does that matter? I've also tried formatting individual and all cells as general and number. -- -Jesse CH "Mike H" wrote: Jesse, It doesn't, it displays Yes when b2 = 3 so there is something wrong with the way you are seeing B2. As a start try this. Delete the formula from B2, format the cell as general and enter the number 3. Does B3 change to yes? If it does then out your formula back in B2 and it should work. Mike -- -Jesse CH |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2000, RAND and IF functions do not work together for me
So whether the cell is formatted to 0 decimal places or not, the IF function
checks all decimals. Dang. Thank you, though. -- -Jesse CH "Mike H" wrote: Jesse, See my other reply, you never will, there are just too many numbers between 1 and 6 for it to be likely you will ever hit any integer let alone the one you want 3. Mike |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2000, RAND and IF functions do not work together for me
Sorry - I keep posting replies before I see your latest comment, making my
replies belated. This does indeed fix my problem. Thanks! -- -Jesse CH "Mike H" wrote: Jesse, I missed one of your points. Formatting with zero decimal places has no effect except to alter the way you see the number, the underlying number is un-changed. If you really want to see a 3 change your formula to this and tap F9 a few times. Using the Int function reduces it to 5 options. =INT(RAND()*(6-1)+1) Mike "Jesse CH" wrote: When the cell B2 is reformatted to general and the number 3 is entered, I do indeed see Yes. However, when I put the RAND function back in to B2, it still returns as FALSE. I have formatted the cell as Number with 0 decimal places, does that matter? I've also tried formatting individual and all cells as general and number. -- -Jesse CH "Mike H" wrote: Jesse, It doesn't, it displays Yes when b2 = 3 so there is something wrong with the way you are seeing B2. As a start try this. Delete the formula from B2, format the cell as general and enter the number 3. Does B3 change to yes? If it does then out your formula back in B2 and it should work. Mike -- -Jesse CH |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2000, RAND and IF functions do not work together for me
your welcome
"Jesse CH" wrote: Sorry - I keep posting replies before I see your latest comment, making my replies belated. This does indeed fix my problem. Thanks! -- -Jesse CH "Mike H" wrote: Jesse, I missed one of your points. Formatting with zero decimal places has no effect except to alter the way you see the number, the underlying number is un-changed. If you really want to see a 3 change your formula to this and tap F9 a few times. Using the Int function reduces it to 5 options. =INT(RAND()*(6-1)+1) Mike "Jesse CH" wrote: When the cell B2 is reformatted to general and the number 3 is entered, I do indeed see Yes. However, when I put the RAND function back in to B2, it still returns as FALSE. I have formatted the cell as Number with 0 decimal places, does that matter? I've also tried formatting individual and all cells as general and number. -- -Jesse CH "Mike H" wrote: Jesse, It doesn't, it displays Yes when b2 = 3 so there is something wrong with the way you are seeing B2. As a start try this. Delete the formula from B2, format the cell as general and enter the number 3. Does B3 change to yes? If it does then out your formula back in B2 and it should work. Mike -- -Jesse CH |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula in Excel 2000 to work %'s | Excel Worksheet Functions | |||
Excel 2000 - Formulas and Functions | Excel Discussion (Misc queries) | |||
EXCEL 2000 - COPY & PASTE FUNCTIONS! | Excel Discussion (Misc queries) | |||
Can't get SUM function to work in Excel 2000 | New Users to Excel | |||
Excel 2000 Functions | Excel Worksheet Functions |