Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Brad
 
Posts: n/a
Default normalizing data -- a twist

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


  #2   Report Post  
Bill Kuunders
 
Posts: n/a
Default

Not 100 % sure whether this is what you're looking for but it might get you
on the way.
Normalising the pos numbers by dividing them by the pos range and the neg's
by the neg range
otherwise return zero.
=IF(A1310,A131/LARGE($A$131:$A$145,1),IF(A131<0,-A131/SMALL($A$131:$A$145,1),0))

Greetings from New Zealand
Bill K

"Brad" wrote in message
...
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




  #3   Report Post  
mangesh_yadav
 
Posts: n/a
Default


Another method:
Find the absolute value of you column A, and use your formula on this
second column.

Col B = ABS(A131)
Col C =
=IF(B1310,B131/LARGE($B$131:$B$145,1),IF(B131<0,-B131/SMALL($B$131:$B$145,1),0))


Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=375313

  #4   Report Post  
Bill Kuunders
 
Posts: n/a
Default

Mangesh
I wonder........
How can B131 be smaller than 0 if it is the absolute of A131?

--
Greetings from New Zealand
Bill K

"mangesh_yadav"
wrote in message
news:mangesh_yadav.1pxlue_1117602305.8172@excelfor um-nospam.com...

Another method:
Find the absolute value of you column A, and use your formula on this
second column.

Col B = ABS(A131)
Col C =
=IF(B1310,B131/LARGE($B$131:$B$145,1),IF(B131<0,-B131/SMALL($B$131:$B$145,1),0))


Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile:
http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=375313



  #5   Report Post  
Mangesh Yadav
 
Posts: n/a
Default

Hi Bill,

You are right. I didn't take a second look at the formula. Just saw that
using the ABS() will solve the problem of the OP.

Mangesh



"Bill Kuunders" wrote in message
...
Mangesh
I wonder........
How can B131 be smaller than 0 if it is the absolute of A131?

--
Greetings from New Zealand
Bill K

"mangesh_yadav"


wrote in message
news:mangesh_yadav.1pxlue_1117602305.8172@excelfor um-nospam.com...

Another method:
Find the absolute value of you column A, and use your formula on this
second column.

Col B = ABS(A131)
Col C =

=IF(B1310,B131/LARGE($B$131:$B$145,1),IF(B131<0,-B131/SMALL($B$131:$B$145,1
),0))


Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile:
http://www.excelforum.com/member.php...o&userid=10470
View this thread:

http://www.excelforum.com/showthread...hreadid=375313







  #6   Report Post  
Harlan Grove
 
Posts: n/a
Default

Brad wrote...
I have the following data in the left-most column and the results of a
formula in the next:

....
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.


What are you trying to accomplish by this? If your data values span
positive and negative values, then they must also span zero values
unless the underlying process is discontinuous at zero, but in that
case you should never get a zero data value.

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 guess this means that you're unconcerned about the relative
absolute values of the averages of positive and negative values, e.g.,
if positive values spanned 0.05 to 1.8 while negative values spanned
-0.001 to -0.080, you'd be unconcerned that normalized 1.0 corresponded
to original 1.8 while normalized -1.0 corresponded to -0.080. If so,
try the array formula

=A131/MAX(IF(SIGN($A$131:$A$145)=SIGN(A131),ABS($A$131:$ A$145)),--(A131=0))

  #7   Report Post  
Tushar Mehta
 
Posts: n/a
Default

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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Pull Current Month's Data Out of List - Repost Karl Burrows Excel Discussion (Misc queries) 4 May 3rd 05 01:06 AM
Line Graph Data Recognition Nat Charts and Charting in Excel 2 April 30th 05 02:07 PM
Add data to cell w/o loosing initial data jaycain Excel Discussion (Misc queries) 2 March 29th 05 02:23 AM
Running Data Table using an input that triggers DDE linked data [email protected] Excel Discussion (Misc queries) 1 December 16th 04 12:56 PM
Extending a Chart Data Series from an Array - Can it be done? Jon Peltier Charts and Charting in Excel 4 November 30th 04 04:30 AM


All times are GMT +1. The time now is 12:58 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"