Dunno if you are still looking for a solution...

Based on your current results, the algorithm you are using is (X-Xmin)/
(Xmax-Xmin), which is what I would have expected.

In article ,
says...

Since the zero value would normally not be the middle value of the range of
numbers, I think if I could come up with a formula to normalize all the
values greater than 0 with a range of 0 to 1, then all negative numbers 0
to -1, and 0= 0, I'd have what I am looking for. I'd appreciate any thoughts
here and my thanks in advance. Brad

You need to clarify what you want to do. When you write that the
positive numbers should be in the range 0 to 1, does that mean you want
to lowest number to be 0? If so, subsequent to the normalization, you
could land up with multiple zeros. How will you distinguish between
them? In any case, if that's what you want...

Suppose your data are in B3:B17. Define a bunch of names (Insert |
Name Define...)

Vals =Sheet1!$B$3:$B$17
NegVals =IF(Vals<0,Vals)
PosVals =IF(Vals0,Vals)
NegValsMax =MAX(NegVals)
NegValsMin =MIN(NegVals)
PosValsMax =MAX(PosVals)
PosValsMin =MIN(PosVals)


Now, enter, say in G3, the formula
=IF(B3<0,-(B3-NegValsMin)/(NegValsMax-NegValsMin),IF(B30,(B3-
PosValsMin)/(PosValsMax-PosValsMin),0))

Copy G3 as far down G as necessary.

You will now have one set of values going from -1 to 0, one going from
0 to 1 and one consisting of 0.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
I have the following data in the left-most column and the results of a
formula in the next:

0.006149 0.710222
0.004668 0.676867
-0.00354 0.49206
-0.01529 0.227635
-0.01644 0.201561
-0.0195 0.132744
-0.01835 0.15867
-0.0254 0
-0.02151 0.087515
-0.01532 0.226867
-0.00885 0.372547
0 0.571774
0.011987 0.841663
0.016434 0.941773
0.01902 1


The formula normalizes the first column data so that the values in the
second column are valued between 1 and 0. The formula is:

=(A131-SMALL($A$131:$A$145,1))/(LARGE($A$131:$A$145,1)-SMALL($A$131:$A$145,1))

My problem is that, in the event that there is a zero value in the column of
numbers (the numbers almost always include positive and negative values), I
would like the normalizing formula to show a zero value in the results
there. Note that the actual 0 datum when normalized through this range,
equals .571774.

Since the zero value would normally not be the middle value of the range of
numbers, I think if I could come up with a formula to normalize all the
values greater than 0 with a range of 0 to 1, then all negative numbers 0
to -1, and 0= 0, I'd have what I am looking for. I'd appreciate any thoughts
here and my thanks in advance. Brad