Take the 2-minute tour ×
Mathematics Stack Exchange is a question and answer site for people studying math at any level and professionals in related fields. It's 100% free, no registration required.

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.

share|improve this question
    
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

2 Answers 2

up vote 0 down vote accepted

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

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.