Fix Excel RAND Displaying Only 0 or 1 by Changing Cell Format
Question details
The user needs to display the decimal values returned by the Excel RAND() function instead of seeing only 0 or 1.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet uses the RAND() formula to generate random values from 0 up to, but not including, 1.
- Observed behavior
- RAND() appears to return only 0 or 1 because the cell display is rounded to zero decimal places.
Select a cell containing =RAND() and check the formula bar to confirm that the formula is present. The displayed value can differ from the underlying decimal value when the cell is formatted with no decimal places.
Format the Excel RAND cell to show decimal places
Use this solution when RAND() displays only whole numbers even though the formula should return a decimal between 0 and 1.
RAND() returns a decimal value greater than or equal to 0 and less than 1. A Number format with zero decimal places rounds the displayed result, so values may appear as either 0 or 1.
Click the cell or select the range that contains the =RAND() formula.
On the Home tab, open the Number Format drop-down and choose More Number Formats, or press Ctrl+1 to open the Format Cells dialog box.
On the Number tab, select General, or select Number and set Decimal places to 2 or more.
Click OK. The cells will display decimal RAND() results such as 0.3487 or 0.9125.
Show RAND() Decimal Values in WPS Spreadsheets
WPS Spreadsheets lets you use the RAND() function and quickly change a cell from a zero-decimal number format to General or a Number format with visible decimal places.
- 1. Enter or select the RAND formula: In WPS Spreadsheets, click a cell containing =RAND() or enter =RAND() in a blank cell and press Enter.
- 2. Select the affected cells: Click the RAND cell or drag to select all cells whose random values are displaying as whole numbers.
- 3. Open the number format controls: Go to the Home tab and locate the Number Format box in the ribbon.
- 4. Apply a decimal-friendly format: Choose General, or choose Number and use Increase Decimal until the required decimal places are visible.
Frequently Asked Questions
Why does RAND() show 1 when the function should return less than 1?
RAND() does not return 1. A value such as 0.76 is rounded and displayed as 1 when the cell uses a Number format with zero decimal places.
How do I show more digits after the decimal in Excel?
Select the cells, go to Home > Number, and click Increase Decimal. You can also press Ctrl+1, select Number, and set the Decimal places value.
Does changing the cell format change the RAND() value?
No. Formatting changes only how the result appears in the cell. The underlying RAND() value remains a decimal from 0 up to, but not including, 1.
How can I generate random whole numbers instead of decimal RAND values?
Use RANDBETWEEN(bottom, top), such as =RANDBETWEEN(0,100), to return a random integer within the specified range.
