# How to stop excel from changing a range when you drag a formula down? [closed]

I'm trying to normalize a set of columns of data in an excel spreadsheet.

I need to get the values so that the highest value in a column is = 1 and lowest is = to 0, so I've come up with the formula:

=(A1-MIN(A1:A30))/(MAX(A1:A30)-MIN(A1:A30))

This seems to work fine, but when I drag down the formula to populate the cells below it, now only does A1 increase, but A1:A30 does too.

Is there a way to lock the range while updating just the number I'm interested in?

I've tried putting the Max and min in a different cell and referencing that but it just references the cell under the one that the Max and min are in and I get divide by zero errors because there is nothing there.

-

## closed as off topic by Andy W, Peter Flom♦, whuber♦Jan 30 at 21:40

Questions on Cross Validated are expected to relate to statistics within the scope defined by the community. Consider editing the question or leaving comments for improvement if you believe the question can be reworded to fit within the scope. Read more about reopening questions here.If this question can be reworded to fit the rules in the help center, please edit the question.

A '$' will lock down the reference to an absolute one versus a relative one. You can lock down the column, row or both. Here is a locked down absolute reference for your example. (A1-MIN($A$1:$A$30))/(MAX($A$1:$A$30)-MIN($A$1:$A\$30))