Looking for help on normalizing data from a specific value in another column
Nov 11, 2015 6:52 PM(2308 views)
I need to create a formula that is called relative Lumen flux @ 22.5A. I have one column that has Lumen flux @ various different currents. I want to create a new column that normalizes the lumen flux @ different currents to the flux @ 22.5A. So the formula would be like φv/φv(22.5A) . Please help on this.
To normalize data points at one current relative to that of the 22.5A distribution, you'll start by calculating the mean and standard deviation of Flux for each current group (including the 22.5A group).
Assuming that the Flux values are normally distributed, taking z-scores is then the easiest method of normalizing.
Find the Z-score of a value relative to its current group (e.g. Group A). This gives you the number of standard deviations your value is from its group's mean (all relative to that current group.)
Then take this Z-score and back calculate the x-value relative to the 22.5A group's mean and sigma. The "x-relative" formula shown below calculates these two translations in one step.