14
$\begingroup$

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.

$\endgroup$

1 Answer 1

46
$\begingroup$

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))
$\endgroup$
3
  • 1
    $\begingroup$ Side note: I think it works this way for essentially all modern spreadsheet programs - I know it does for OpenOffice.org Calc and Google Docs' spreadsheet. $\endgroup$
    – Erik P.
    Feb 21, 2011 at 17:40
  • 2
    $\begingroup$ Good answer--but it (and the original question) belong on a different site. $\endgroup$
    – whuber
    Feb 21, 2011 at 19:04
  • 2
    $\begingroup$ On a windows machine, you can toggle through the "lock down" methods with the F4 key. $\endgroup$
    – Chase
    Feb 21, 2011 at 19:11

Not the answer you're looking for? Browse other questions tagged or ask your own question.