Say we have a set of data in column A.
469
396
600
177
240
155
204
454
278
233
And we want to scale the data so that the lowest value equates to 0 and the highest value equates to 1.
If you just want a quick formula to do this, you can copy the line below. However, you may consider reading further to really understand how it works.
=(A1 - MIN(A:A)) / (MAX(A:A) - MIN(A:A))
First, figure out the minimum value in the set.
=MIN(A:A)
In this case, it’s 155.
Now for each value, figure out the difference from the minimum.
=A1 - MIN(A:A)
Looking at each value…
=A1 - MIN(A:A) = 469 - 155 = 314
=A2 - MIN(A:A) = 396 - 155 = 241
=A3 - MIN(A:A) = 600 - 155 = 445
=A4 - MIN(A:A) = 177 - 155 = 22
=A5 - MIN(A:A) = 240 - 155 = 85
=A6 - MIN(A:A) = 155 - 155 = 0
=A7 - MIN(A:A) = 204 - 155 = 49
=A8 - MIN(A:A) = 454 - 155 = 299
=A9 - MIN(A:A) = 278 - 155 = 123
=A10 - MIN(A:A) = 233 - 155 = 78
Next, figure out the largest difference. This is simply the different between MAX(A:A) and MIN(A:A).
=MAX(A:A) - MIN(A:A) = 600 - 155 = 445
For each value, calculate what percentage it’s difference is when compared to this maximum difference.
=(A1 - MIN(A:A)) / (MAX(A:A) - MIN(A:A)) = (469 - 155) / 445
=(A2 - MIN(A:A)) / (MAX(A:A) - MIN(A:A)) = (396 - 155) / 445
=(A3 - MIN(A:A)) / (MAX(A:A) - MIN(A:A)) = (600 - 155) / 445
=(A4 - MIN(A:A)) / (MAX(A:A) - MIN(A:A)) = (177 - 155) / 445
=(A5 - MIN(A:A)) / (MAX(A:A) - MIN(A:A)) = (240 - 155) / 445
=(A6 - MIN(A:A)) / (MAX(A:A) - MIN(A:A)) = (155 - 155) / 445
=(A7 - MIN(A:A)) / (MAX(A:A) - MIN(A:A)) = (204 - 155) / 445
=(A8 - MIN(A:A)) / (MAX(A:A) - MIN(A:A)) = (454 - 155) / 445
=(A9 - MIN(A:A)) / (MAX(A:A) - MIN(A:A)) = (278 - 155) / 445
=(A10 - MIN(A:A)) / (MAX(A:A) - MIN(A:A)) = (233 - 155) / 445
The result is what we’re looking for.
=(A1 - MIN(A:A)) / (MAX(A:A) - MIN(A:A)) = 0.71
=(A2 - MIN(A:A)) / (MAX(A:A) - MIN(A:A)) = 0.54
=(A3 - MIN(A:A)) / (MAX(A:A) - MIN(A:A)) = 1.00
=(A4 - MIN(A:A)) / (MAX(A:A) - MIN(A:A)) = 0.05
=(A5 - MIN(A:A)) / (MAX(A:A) - MIN(A:A)) = 0.19
=(A6 - MIN(A:A)) / (MAX(A:A) - MIN(A:A)) = 0.00
=(A7 - MIN(A:A)) / (MAX(A:A) - MIN(A:A)) = 0.11
=(A8 - MIN(A:A)) / (MAX(A:A) - MIN(A:A)) = 0.67
=(A9 - MIN(A:A)) / (MAX(A:A) - MIN(A:A)) = 0.28
=(A10 - MIN(A:A)) / (MAX(A:A) - MIN(A:A)) = 0.18
As you can see, the lowest value 155 is scaled to 0.00 while the highest value 600 is scaled to 1.00. All the other values are proportionately somewhere in between.
This can be useful when used on multiple columns to normalize the data and make scores or comparisons.
Once again, the final formula is:
=(A1 - MIN(A:A)) / (MAX(A:A) - MIN(A:A))