RESCALE Function for Excel

Work in Progress :: Please contact us to report errors, typos, etc.

=L_RESCALE(array, lower, upper)

Argument	Description	Example
array	A 1D or 2D range of values	B1:D5
lower	The lower bound of the scaled array	-5
upper	The upper bound of the scaled array	5

Description

Rescaling an array using a process called "min-max normalization" is a common task when you want to compare data sets that are at very different scales. Normalization typically involves scaling to [0,1], but there are situations where you may want to rescale to different bounds. Examples may include:

Sensitivity Analysis: Plotting the effect of multiple variables on a single output in a single chart by scaling all the inputs to a range of [-1,1].
Finance: Rescaling stock prices from different companies to compare price movement and volatility.
Data Visualization: Scaling data to the same [lower,upper] bounds may aid in comparing plots such as heat maps.
Image Processing: Scaling pixel intensities to [0,1] to help in image enhancement techniques and training learning models.
Signal Processing: Compare different signals on a common scale.
Grade Curve: A quick-and-dirty method for curving grades on an exam ( eg: change values from a range of [30,85] to a range of [70,95] )
Machine Learning & Optimization: Algorithms often perform better when scaled to a standard range, especially those relying on distance calculations (gradient descent, k-nearest neighbors, etc.)
User Interface Controls: For slider bars, you may want to convert an input between [0,100] to some other range such as [1%,10%].

Rescaled Value = Lower + (Upper - Lower) * (Value - MIN(array)) / (MAX(array) - MIN(array))

When Lower = 0 and Upper = 1, this simplies to:

Rescaled Value = (Value - MIN(array)) / (MAX(array) - MIN(array))

This calculation is performed for each value of the array.

Lambda Function Code

This code for using L_RESCALE in Excel is provided under the License as part of the LAMBDA Library, but to use just this function, you may copy the following code directly into your spreadsheet.

Code to Create Function via the Name Manager

Name: L_RESCALE
Comment: Returns an array with values rescaled to [lower,upper]
Refers To:

=LAMBDA(array,lower,upper,
LET(doc,"https://www.vertex42.com/lambda/rescale.html",
    min,MIN(array),max,MAX(array),
    lower+(upper-lower)*(array-min)/(max-min)
))

Code for AFE Workbook Module (Excel Labs Add-in)

/**
* Returns an array with values rescaled to [lower,upper]
* L_RESCALE({1,2,3},0,1) = {0, 0.5, 1}
*/
L_RESCALE = LAMBDA(array,lower,upper,
LET(doc,"https://www.vertex42.com/lambda/rescale.html",
    min,MIN(array),max,MAX(array),
    lower+(upper-lower)*(array-min)/(max-min)
));

Named Function for Google Sheets

Name: L_RESCALE
Description: Returns an array with values rescaled to [lower,upper]
Arguments: array, lower, upper (see above for descriptions and example values)
Function:

LET(doc,"https://www.vertex42.com/lambda/rescale.html",
    min, MIN(array), max, MAX(array),
    ARRAYFORMULA(lower+(upper-lower)*(array-min)/(max-min))
)

Warning

These L_RESCALE functions are not compatible between Excel and Google Sheets. The GS version requires ARRAYFORMULA to return multiple results.

L_RESCALE Examples

Curved Test Scores

In this example, a teacher has a set of 5 exam scores that range from 45 to 88. The teacher decides to grade this exam on a curve, and wants to scale the values to [70,95].

A1:A5 = {74; 88; 67; 45; 81}
B1 = L_RESCALE(A1:A5, 70, 95)

Result: {86.86; 95; 82.79; 70; 90.93}

The lowest original score of 45 became 70, and the highest original score of 88 became 95.

Test: Copy and Paste this LET function into a cell
=LET(
    array, {74; 88; 67; 45; 81},
    L_RESCALE(array, 70, 95)
)

L_RESCALE