Normalising multiple values on a scale

[MikeRoot]

Hi there,

I have a bit of a issue I am trying to resolve.

I work in a market research and we work with clients interpreting data for them. What I am trying to achieve is a Key Performance Indicator summary, which gives a score out of 10 regardless of the input number i.e. all of them are originally percentages.

Firstly, I need to basically re-assing these percentage values to a score out 10, while keeping the relative difference between them. I can pretty much do this.

The bit where I am stuck is they have benchmark quartiles that they then lay over the top of the data. These will vary i.e. the UK benchmark scores will differ to the US benchmark scores. These are quartiles, so the client wants quartiles values to be re-assigned to 2.5 (out of ten), Q2 to 5 (out of ten) and Q3 to 7.5 (out of ten).

For example:

The results of a study we run may be:

Household owners = 45%
Automotive Owners = 13%
Pet owners = 27%
Mobile phone owners = 95%

UK benchmark values:

Household owners: Q1 (35%), Q2 (44%), Q3 (70%)
Automotive owners: Q1 (8%), Q2 (22%), Q3 (30%)
Pet owners: Q1 (11%), Q2 (42%), Q3 (51%)
Mobile phone owners: Q1 (79%), Q2 (81%), Q3 (93%)

The based on this, the scores in the summary visual will be roughly (something like):

Household owners = 5.0
Automotive Owners = 3.3
Pet owners = 3.7
Mobile phone owners = 8.3

Something like this, but in an automated fashion, so that it can cater for differing values, etc. Any help would be appreciated!

Mike

[MrShorty]

I find that I first need to understand the problem mathematically before doing it in Excel.

1st guess at an algorithm is that you want a basic linear interpolation algorithm.

Please Login or Register  to view this content.

Then you want to "lookup" where your study score/percent fits in the "known x" column, then use linear interpolation to compute the score "known y". Does that sound right?

If that is correct, one way I have used to perform linear interpolations is to compute the "slope" and "intercept" for each pair of entries in adjacent columns, then structure a lookup function to pull the slope and intercept from the lookup table. known y can then easily be calculated from the study percent. Here's a similar example I put together for someone else:http://www.excelforum.com/excel-char...ml#post3904113 note in particular the lookup table in columns H, I, J.