Well, the normalization function is right , but even if it weren't, your solution should yield exactly the same results, assuming that you've transformed the weightings correctly.

Well, they

*don't* yield the same results, that is my problem with the 10-0 system.

Take this example. We are down to two states, A and B, and 3 equally-important criteria (in an abstract sense), a, b and c. Let's further state that A is (very) slightly less good on criteria a and b, and B is very much less good on criterion c.

The correct answer is state A, of course; we know that intuitively. And that is what Ted's normalization gives, with no fudging of weights (which we assume here are all 1):

Criteria -> a b c Total

State A 9.9 9.9 10 29.8

State B 10 10 2 22

The 10-0 normalization gives the wrong answer, State B:

Criteria -> a b c Total

State A 0 0 10 10

State B 10 10 0 20

Now you will say, we need to boost the weight for criterion c (or depress a and b) to give the right answer, and of course we can do that. But this is a simple example, we

*already know* what the right answer is intuitively, so we pick the appropriate criterion and keep boosting away until the spreadsheet yields the correct answer.

But then, why bother with the spreadsheet? The whole point of the thing is for

**it** to tell

**us** the right answer, not the other way around. We don't know the right answer, and we don't know which criteria weight to fudge and how much, and anyway when we are fudging to get one state in line we inadvertently get another state out of line, so the whole thing becomes an unwieldy mess.

Sorry, Jason, I just don't believe the function of weights is to compensate for unhelpful normalizations. It is to decide what is important to you, and that's it.

you wrote in that other thread,

I don't think it's possible to consider how to weight variables without some knowledge of the base numbers & what they mean.

OK, I'll buy that, now that I think about it. You can get some idea what your weight should be by knowing what the data are saying. But that is a far cry from fudging them to compensate for inadvertently having knocked a viable state out of contention!

You also wrote this:

But even if you think you should just consider variables abstractly to determine their weightings, the current method of interpolation is better than Ted's suggested method, which requires not just a consideration of the variables' importance "abstractly" or "in themselves" but also how the scale of the base variable is affecting the transformation of the data."

I don't know what that means. The normalization is supposed to deal with the scale.

You wrote this, which I didn't look at too closely at the time:

But Ted's solution can create paradoxes if you're not very sophisticated about the way in which you do the ranking. For example, imagine the ranking is "government ownership above 48%." Then state A scores 2% and state B scores 1% even though the fundamental, underlying concept is the same. Ted's solution would give B a 5 and A a 10. But any good normalization should not change if the scale of the fundamental variable changes.

Examining this again, I see that your implementation of Ted's method in fact does give a more reasonable result. The 10-0 algorithm would assign a 10 to A and a 0 to B. A and B are really near equivalent, and Ted's algorithm more closely approximates that reality than the 10-0 algorithm does. But of course the real way this would be normalized would be proportionately (referenced to 0, not 48), with a simple extra provision that zero is returned if the value is 48 or under.

I will provide in my cut of the spreadsheet, a cell with a 10-0 algorithm so you can cut and paste it as you like.