Exploring the Goodness of Fit in Linear Models - The Excel RANDBETWEEN Function

Scott A. Sinex

I provide the basics of developing a computational interactive Excel spreadsheet, including an Excel tutorial, in Sinex (2004). In this example the new twist is the use of the RANDBETWEEN function (available in the function editor) to add noise or scatter to the data. (See the Microsoft Excel Help, and enter the keyword “randbetween” for instructions to load this function, which is part of the Analysis ToolPak.)

This function returns a random number between two set limits placed in parentheses, such as RANDBETWEEN (-10, 10). The RANDBETWEEN function is a perfect way to add noise, as random variation, to the data, and the noise is recalculated each time the function is applied. The effect or size of the error can be controlled by an adjustable variable ($H$2 in Figure 4), controlled by the scroll bar and multiplied by the RANDBETWEEN function. Figure 4 shows a sample formula.

Figure 4. Sample Excel formula using RANDBETWEEN
to add noise to a linear function