Tell me more ×
Cross Validated is a question and answer site for statisticians, data analysts, data miners and data visualization experts. It's 100% free, no registration required.
up vote 1 down vote favorite

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.

share|improve this question

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 in the FAQ. 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 closed questions here.

1 Answer

up vote 13 down vote accepted

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))
share|improve this answer
Amazing thanks. – Omar Kooheji Feb 21 '11 at 17:17
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. – Erik P. Feb 21 '11 at 17:40
2  
Good answer--but it (and the original question) belong on a different site. – whuber♦ Feb 21 '11 at 19:04
On a windows machine, you can toggle through the "lock down" methods with the F4 key. – Chase Feb 21 '11 at 19:11

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