Help with excel please

[Stuberry]

Hi guys,

I'm currently in Bangalore doing some volunteer work for a children's charity as a sports and adventure leader.

I've also taken on some stats work, assessing the quality of life of some of the children. This is where I need your help.

The children have filled in a questionnaire, and their answers have been used to produce a "raw score". This score is then compared to a table to give a "transformed score".  I want to tell excel to make this conversion from raw score to transformed score for me. But I cannot see any mathematical correlation between the two so I can't use a formula.

Does anyone know how I can do this?

The table is attached. I'm using the 0-100 score. 

[daniel_kemble]

Can you mail me the excel spreadsheet I may be able to help

[JimC]

Does anyone know how I can do this?

Those are tables to translate the results are they? If I understand the rquirement right then I'd use vlookup. Create a new tab on the spreadsheet, and past each table on it. Select the actual table data on each one (not the heading) and select insert/name/define and fill in domain1, domain2 etc as the name for each little table.

Then to do the actual translation use a formula like
=vlookup(A1,domain1,3,false)
 (or domain2 etc)
A1 being the cell with the original score, the 3 telling it to use the value in the third column, and false telling you that you want an n/a result if you don't get an exact match.
Thus if A1 contained 16 then the formula would return 31.

The names aren't completely essential, you can just put the range formula in, but they make it easier if you are copying the formula from place to place.

[Will_Lee]

Right Stu,

I have done the first ones for all four domains.

Stand by for the other ones.

[Will_Lee]

OK. Rough and ready spreadsheet herewith.

There is a linear relationship between the input and the each of the outputs, but the output is rounded to a whole number to confuse you. In the 0-100 scale it has been rounded at least twice, to confuse you at least twice.

Note: I have done it with one input and 8 outputs. This means that when you put in a "Domain 1" number, you will get the two outputs you want, and two useless outputs for each of the three other domains. Ignore these!

Note: It's too big to go plain, so I've had to zip it up. You'll have to find a way to unzip it at your end.

Have fun!

[ghislain_devouthon]

Right Stu,

I have done the first ones for all four domains.

Stand by for the other ones.

Is that how you are preparing your exams 

Will future Excel professor.

[Stuberry]

Will, you are a complete legend. Thank you so much.

I copied specific parts of your spreadsheet into my spreadsheet, and attached is what I have produced.

This is a fantastic achievement and means we have actual figures to show how we are improving these kids lives. This should boost our fund raising meaning we can help more kids.

By the way, at the Indian NGO of the year awards, we won South Indian Small NGO of the Year 2008.

[Will_Lee]

No problem Stu - a pleasure.

When can we expect you back?

[Stuberry]

Umm, hopefully about June/July. But I will be totally skint and may only be able to make the nats...