UK-Cherub Forum

Off Topic => Not Boats => Topic started by: Stuberry on March 14, 2008, 08:01:23 AM

Title: Help with excel please
Post by: Stuberry on March 14, 2008, 08:01:23 AM: 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.
Title: Re: Help with excel please
Post by: daniel_kemble on March 14, 2008, 10:18:15 AM: Can you mail me the excel spreadsheet I may be able to help
Title: Re: Help with excel please
Post by: JimC on March 14, 2008, 04:26:24 PM: Quote from: stu_tinner on March 14, 2008, 08:01:23 AM
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.
Title: Re: Help with excel please
Post by: Will_Lee on March 14, 2008, 08:23:15 PM: Right Stu,

I have done the first ones for all four domains.

Stand by for the other ones.
Title: Re: Help with excel please
Post by: Will_Lee on March 15, 2008, 01:40:25 AM: 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!
Title: Re: Help with excel please
Post by: ghislain_devouthon on March 15, 2008, 08:41:31 AM: Quote from: Will_Lee on March 14, 2008, 08:23:15 PM
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 :D ;D

Will future Excel professor.
Title: Re: Help with excel please
Post by: Stuberry on March 24, 2008, 08:15:45 AM: 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.
Title: Re: Help with excel please
Post by: Will_Lee on March 24, 2008, 10:41:36 AM: No problem Stu - a pleasure.

When can we expect you back?
Title: Re: Help with excel please
Post by: Stuberry on March 25, 2008, 08:49:17 AM: Umm, hopefully about June/July. But I will be totally skint and may only be able to make the nats...