calculate averages

[cflorackis]

Dear friends

I have the following Table and I need to calculate the average values of the weight of specific subgroups of individuals (e.g. average weight of males that are over 20 years old). Is there a way to do that without filtering the data all the time and copying and pasting information to different spredsheets. Also, is there a way to create new columns that only contain the innformation required each time (e.g. list of the weight of those individuals that are males over the age of 20)

Than you very much in advance

aaaaaaaaaaa.xls
	A	B	C	D	E
1	person	gender	age	weight
2	1	male	25	80
3	2	female	26	58	average weight of males >20 years old
4	3	female	32	84	average weight of males >30 years old
5	4	female	14	78	average weight of males >40 years old
6	5	female	16	90	average weight of males >50 years old
7	6	male	85	125
8	7	male	80	150
9	8	female	92	88	average weight of females >20 years old
10	9	male	17	59	average weight of females >30 years old
11	10	male	48	88	average weight of females >40 years old
12	11	female	23	47	average weight of females >50 years old
13	12	male	28	49
14	13	female	45	150
15	14	female	78	98
16	15	male	82	95
17	16	female	19	69
18	17	male	52	82
Sheet1

 

[Pekkavee]

Did you ever check the text format thing i told you ?

Pekka

 

[cflorackis]

Yes pekka ,

I checked it my I could not really understand what you mean

 

[Andrew Poulsom]

I can get 74.75 with rows 2, 11, 13 and 18.

Some things to try:

1. Right click the header for column C, choose Data|Text to Columns and click Finish.

2. Repeat the above for column D.

If the result is still incorrect after converting possible text entries to values:

3. In a spare column on row 3 enter:

=B3=B$2

and copy down. Rows 7, 8, 10, 11, 13, 16 and 18 should contain TRUE. If not change the entries in column B to "male". They could for example have a trailing space.

 

[Pekkavee]

Yes pekka ,

I checked it my I could not really understand what you mean

I mean that some of your numbers are text. Excel doesn't see them as numbers and can not count them.

You have to change them into numbers again as I told you by multiplying them by number 1 or as Andrew just told you.

Pekka