# Creating indices

Is there a "proper" formula for creating indices? I need to compute series of numbers into a KPI that can be tracked over time.

Example dataset is like this:

A   B   C       D
-----------------
3   20  10000   ?
1   3   2000    ?
9   100 20000   ?
5   20  1000    ?


I need to come up with a formula to distill A, B, C to D which is an index that indicates which row has the highest "score" based on following rules:

1. Column A - Lower the better - 3rd most important factor
2. Column B - Higher the better - 2nd most important factor
3. Column C - Higher the better - 1st most important factor

The problem I have is that how to come up with a proper weights in the formula so that it always follows the importance order.

-
Isn't this just sorting with respect the columns C (descending), B (descending) and A (ascending) in that particular order, which is something statistical software can do for you? –  Stefan Hansen Jan 8 '13 at 8:42
Yes but the challenge is how to do the sorting with code. –  James D. Jan 8 '13 at 10:13
In R: if x is a matrix consisting of $n$ rows and $3$ columns, then x[order(-x[,3],-x[,2],x[,1]),] does the job. –  Stefan Hansen Jan 8 '13 at 10:19
Thanks. The problem still is that now that I've matrix sorted, I still don't have any number which I could use as a KPI. Sorting with ready-made functions is fine if you just need to output the result but in this case I need to do more. –  James D. Jan 8 '13 at 10:44
What about the first row in the sorted matrix gets $D=1$, the second row get $D=2$ and so on? –  Stefan Hansen Jan 8 '13 at 10:46

We are searching for a function $F:(a,b,c)\rightarrow d$ such that

$F(a_i,b_i,c_i)>F(a_j,b_j,c_j)$

if ($c_i>c_j$) or $((c_i=c_j)and(b_i>b_j))$ or $((c_i=c_j)and(b_i=b_j)and(a_i<a_j))$

As has been pointed out, this is not possible if we allow $a,b,c \in\mathbb{R}$

However it is possible if $a,b,c\in\mathbb{N}$ which looking at your sample dataset may well be the case.

If you can also specify maximum values s.t.

$a_i<A_{max}$, $b_i<B_{max}$

Then there is a very simple formula $$F(a,b,c)=(A_{max}-a)+A_{max}(b+cB_{max})$$ If you cannot guarantee maximum values then you can use this formula:- $$F(a,b,c)=c+f(b-f(a))$$ $$f(x)=\frac{x}{1+x}$$

If you also need to allow for negative numbers (but still only integers) then you will need $f$ to be a strictly monotonic function $\mathbb{R}\rightarrow(0,1)$ such as $$f(x)=\frac1{1+e^{-x}}$$

-

There is no such index or formula, and for economists there is well known reason: Lexicographic preferences cannot be represented by a utility function.

To see this, note that for each value for C we need an interval of values for D to rank all possible rows with the given C-value. Since there are uncountably many possible values for C, we need uncountably many disjoint intervals, which is not possible.

-