Normalizing Columns

A number of normalization methods can be written as expressions or used as a transformation step when adding data tables. See the links at the end of this topic for a description of the theory behind each method.

In the expression examples below, the following values are used:

Columns: E and A, where E is the column to normalize and A is a baseline column.

Percentile value: P

Normalize by mean

[E] / Avg([E])

[E] * Avg([A]) / Avg([E])

Normalize by trimmed mean

[E] / TrimmedMean([E], P)

[E] * TrimmedMean([A], P) / TrimmedMean([E], P)

Normalize by percentile

[E] / Percentile([E], P)

[E] * Percentile([A], P) / Percentile([E], P)

Scale between 0 and 1

If( Max([E]) = Min([E]), 0.5, ([E] – Min([E]) / (Max([E]) – Min([E])) )

Subtract the mean

[E] – Avg([E])

Subtract the median

[E] – Median([E])

Normalization by signed ratio

If( [E] > [A], [E] / [A], -[A] / [E])

Normalization by log ratio

Log10( [E] / [A] )

Normalization by log ratio in standard deviation units

Log10( [E] / [A] ) / StdDev(Log10( [E] / [A] ))

Z-score calculation

([E] – Avg([E])) / StdDev([E])

Normalize by standard deviation

[E] / StdDev([E])