Tips for Spreadsheets

*Summary: Use the RANDBETWEEN function instead of RAND to more easily generate random numbers in your Microsoft Excel 2003 spreadsheet.*

While developing a spreadsheet in Excel 2003, when forecasting or for other reasons, you may need to generate random numbers in certain cells. While this can be done using a function called RAND and applying a mathematical formula to generate numbers between a given range, a shortcut formula exists called RANDBETWEEN. The syntax:

**=randbetween(LOW,HIGH)**

* Replace LOW with the lowest number you want generated.

* Replace HIGH with the highest number you want generated.

For example, to simulate the roll of a standard six-sided dice:

**=randbetween(1,6)**

Note that this formula works with positive and negative LOWs and HIGHs, and it will only generate integer numbers. If you need random numbers involving decimals, either use the RAND function or perform division on the result of the RANDBETWEEN function with the following formula:

**=randbetween(LOW*10^PRECISION,
HIGH*10^PRECISION)/(10^PRECISION)**

* Replace PRECISION with the levels of decimal precision needed (i.e. if you need numbers like 1.1 or 1.8, PRECISION would be 1. For numbers like 1.235 or 1.937, PRECISION would be 3).

For example, if you need random monetary values from $5.00 to $9.00, these can have up to two levels of decimal precision (.00 to .99). Thus, the formula would be:

Or simplified:

NOTE: If the RANDBETWEEN formula does not work in your spreadsheet or returns a cell with the result of "#NAME", you need to install the Analysis Toolpak Add-In. After installation, press F2 then ENTER to regenerate all formulas.

Add: Del.icio.us | Digg | Furl | My Yahoo!

*Last Modified on: July 13, 2008, at 12:04 A.M. EDT*

Return to the Microsoft Excel 2003 Functions and Formulas page.

Search the Site: