Normalize & Standardize
Nov 21, 2006
How to normalize a simple array (1 column) of 1000+ cells
I tried:
=STANDARDIZE(A2,A3,A4)
A2 the array that I want to normalize (serie of values)
A3 the Arithm. Mean (Average) for the target array (the correct array, same data type)
A4 STDEVP for the target array (the correct array).
But not sure that this formula is for normalizing (shift data set till it fits the histogram bell shape of the target data set)
I was able to do the normalization by hand using Histogram in Excel Statistics and then (linear) shifting my data (adding a constant) till I had a good fit when overlaying the two histograms, but I like to do it with a formula.
Below a sample of the data: the first column is my model (target) the second the data to be normalized and the third column after I subtracted 10 to the second column so it fitted in the histogram of the first column (cannot attach the Excel file as it is too large)
102.383115.18105.18
102.258128.56118.56
101.427140.58130.58
106.019119.7109.7
105.065123.18113.18
105.396128.27118.27
103.949130.46120.46
View 9 Replies
Feb 5, 2009
An account number should consist of 7 digits. Sheet 1-When the data was downloaded from an application any account that contains a zero in front of the number then it would drop the zero and becomes an 6 digits account (note: not all accounts have a zero in front). How can I normalize this so that when I try to reference/lookup to the correct account number it would know to match.
View 3 Replies
View Related
Apr 25, 2009
Can some one please assist. I have a column of alpha numeric numbers with leading zeros which i need to normalize. (remove leading zeros).
They have been copied from another spread sheet where they are formatted as General.
View 6 Replies
View Related
Mar 31, 2013
I have a file of company name exactly duplicated. Within each duplicate set of rows each row can have a different account code (number).
I need to normalise all rows to one account code (any one will do - I can actually do this bit using some commercial software so this bit can be skipped as part of this thread) but also keep every variation/different account code in an 'audit trail' field for each row.
The attached sample file shows the input and desired output i would like.
View 1 Replies
View Related
