0
$\begingroup$

I have an Excel spreadsheet with multiple vectors of 3 numbers. I would like to normalize those vectors so that they are within the same range.

v1 = { 0, 5, 75 } -- [0, 75] v2 = { 2, 4, 6 } -- [2, 6] v3 = { 0, 50, 75 } -- [0, 75] 

In the example above the results we obtained for v1 and v3 were between 0 and 75, but v2 is between 2 and 6. I would like those vectors to be on the same scale.

Could someone please help me with that? Is there an Excel function for that?

Thanks!

  • 0
    While you can multiply a vector by a scalar (just multiply all the entries by some fixed number), you can't change the min and the max to arbitrary specified things unless you are willing to do some other operation. For example, if the min is $0$, it will always be $0$ after multiplication. The kinds of "normalization" that would make sense depend on what your data represents. There are an infinite number of things you could do, but more context is needed to know the "right" thing to do.2011-08-08

1 Answers 1

2

Simplest thing might be to use an affine transformation so that the minimum of each is $0$, the maximum is $100$, and the relative proportions stay the same when the minimum is and maximum are different, and all equal to the midpoint, 50, if all three entries are equal; so that, for example, with $\{0,50,75\}$ you want the middle number to be two thirds of the way between $0$ and $100$.

Say your set contains $a\leq b\leq c$. If $a=b=c$, then normalize it by transforming it into $(50,50,50)$.

If $a\lt c$, then replace each element $x$ with $100\left(\frac{x-a}{c-a}\right)$.

Doing this, your three sets would become $\{0, \frac{20}{3},100\}$, $\{0, 50, 100\}$, and $\{0, \frac{200}{3}, 100\}$.

In other words, given a multiset $S=\{a,b,c\}$, test to see whether $\max(S)=\min(S)$; if so, replace it with the multiset $\{50,50,50\}$. If $\max(S)\neq \min(S)$, then apply the function $x\longmapsto \frac{100(x-\min(S))}{\max(S)-\min(S)}$ to the multiset.

You can choose some other way to deal with the case where $a=b=c$, depending on what you want this for.

  • 0
    Thanks, Arturo, for answering, and for showing that the question really does have a mathematical context.2011-08-09