Creating indices

Question

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.

Answer 1

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}}$$

Answer 2

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.