Transforming data to lie between 0 and 1

**zgall1** · 03-19-2010, 06:31 PM

I have a spreadsheet with approximately 1000 rows. For column A, the range of values runs from 2.91 to 5.9 and column B has values from 1.12 to 2.99. Is there a formula or a series of formulas that I can use so that each value is translated to a score between 0 and 1? For instance, in column A, 2.91 would be scored as 1 and 5.9 would be 0 with all values in between lying between 0 and 1 depending on their relation to the min and max values of 2.91 and 5.9. A similar approach would be used for column B. Thanks for the help.

**shg** · 03-19-2010, 06:42 PM

If the data is in A1:A1000, compute the min =MIN(A1:A1000) and span (=MAX(A1:A1000) - min) in some convenient cells, and then the scaled value in B1 and down is

=(A1 - min)/ span

To reverse the order, calculate instead max and span, then in B1 and down,

=(max - A1) / span