Help using Excel

breffnij

Hi all,

Looking for some help for a problem with excel.

I am taking data from an excel in survey monkey which I am conducting and need to know how to convert my question into usable data.

I am asking people their age and giving them a range in which they fall into e.g. 5 to 10, 20 to 40 etc.

I am wondering is their any way I can get the mean of these numbers by replacing the 'to' with some formula. (for example if I replaced it with + symbol excel will automatically add them together and I'd get the sum of each in that cell). I have my reasons for needing to do this regardless of how silly it sounds.

Anyone know if possible............or more likely willing to tell me. Happy face coming your way if so!

Victor

Two minutes

Victor

I need to work out something for babies as it is skewing all the data

You need to work out how to deal with people under 1 or over 100. Note that all the zeros at the tops are skewing the numbers at the bottom.

The Big Red Button

But I think that what the OP is saying is that their data is in the format

Amanda 5-10
Amy 20-40
Andrew 10-20

etc

i.e. the data is provided in terms of ranges - not in terms of specific ages, as in your spreadsheet?

So something using Text-To-Columns, and a formula getting the mean of the columns, might be better?

OP what format is your data in at the moment?

Sup08

You can make an IF formula to give a result if the cell reference gives a certain age selection, you can then sort the ages by the formula result.

formula
=IF(A1="5 to 10",1,IF(A1="11 to 20",2,(IF(A1="21 to 30",3,4))))

Would give this result. Just keep adding an if for each age group until you reach second last and give the final false the next number for the final age group, as in the example the final age group is 4.

5 to 10 1
11 to 20 2
21 to 30 3
31 to 40 4
5 to 10 1
21 to 30 3
etc.

Sup08

Hope these help

ageresult.PNG