Normalizing a Worksheet Output

[Thomas Scott]

I am trying to use my formula for normalizing a worksheet (via Min-Max method) such that the user does nothing but fill in a worksheet and the results are in another worksheet. The method is to create 3 worksheets, say A,. B, and C. with the primary data in A, a randomly filled dummy dataset n B, and the formulas copied into all matching cells in C but pointing to B. The user would then simply copy their rows and columns into B, delete the excess rows and columns and see their results in C.

I have successfully used the ranging formula for years BUT I always had the min and max as specific values for each column rather than dynamically in a formula.

The result is obviously wrong in the Sample but if I split the formula into numerator and denominator into separate cells the values are perfect for division to get the right answer. The range is 2 with min of 1 and max of 3 and that does not match the normalized values that should range from 0 to 1 and I cannot see how I could get 2.5.

I do not know enough Excel to see where my mistake is and welcome clarity. Thanks.

Sample.xlsx
	A	B
1	Values	Output
2	Value1	3
3	Value2	1
4	Value3	1
5	Value4	3
6	Value5	1
7	Value6	3
8	Value7	2
9	Value8	2
Prime

Sample.xlsx
	A	B
1	Values	OutputRanged
2	Value1	2.5
3	Value2	0.5
4	Value3	0.5
5	Value4	2.5
6	Value5	0.5
7	Value6	2.5
8	Value7	1.5
9	Value8	1.5
Analysis

Cell Formulas
Range	Formula
B2:B9	B2	=(Prime!B2-(MIN(Prime!B:B))/(MAX(Prime!B:B)-MIN(Prime!B:B)))

 

[Cubist]

You have an extra parenthesis before the first MIN().

=(Prime!B2-MIN(Prime!B:B))/(MAX(Prime!B:B)-MIN(Prime!B:B))

 

[Thomas Scott]

Too embarrassing for a reply. Now on I count parentheses.