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

 

[Andrew Poulsom]

For males over 20:

=SUMPRODUCT(--(B2:B18="male"),--(C2:C18>20),D2:D18)/SUMPRODUCT(--(B2:B18="male"),--(C2:C18>20))


Adjust to suit your other requirements. You might want to use cell references instead of "male" and 20.

 

[cflorackis]

thank you for the reply

I think though that the formula does not calculate the correpsonding average value correctly. For instance for the males over 20 it gives the value of 74.75 while the true average weight is 95.57

Any more suggestions?

 

[Andrew Poulsom]

When I tested the formula with your sample data it returned 95.57.

 

[cflorackis]

Something strange is going on here---I have tried it more than 20 times and it always gives me 74.75.

I really do not know what te problem is

 

[Pekkavee]

Are all your numbers really numbers?

They could be formatted as text and it doesn't work.

Make them numbers again by multiplying them by 1. Put number 1 in an extra cell, then Copy - Paste special - Multiply.

Pekka

 

[Brian from Maui]

A picture is worth....

Book1
	B	C	D	E	F
2	m	25	80		95.57143
3	f	26	58		95.57143
4	f	32	84		95.57143
5	f	14	78
6	f	16	90
7	m	85	125
8	m	80	150
9	f	92	88
10	f	17	59
11	m	48	88
12	f	23	47
13	m	28	49
14	f	45	150
15	f	78	98
16	m	82	95
17	f	19	69
18	m	52	82
Sheet1

 

[cflorackis]

Thanks for all the replies and the time spending with my question

I just figured out that the initial formula suggested by Andrew does not work for my sample just because I impose the restriction ..."male" (at least in my file) In all other cases it works perfectly....so there should be a problem with the format.

As far as the formula suggested by Brian is concerned, I cannot make it work----how can I get a number if my formula starts with { e.g.

{=AVERAGE(IF((B2:B18="m")*(C2:C18>20),D2:D18))} ???????

When i copy that in a cell I get just the formula. When i put

={=AVERAGE(IF((B2:B18="m")*(C2:C18>20),D2:D18))} I get #VALUE

I am sure I am missing somethng here but i am not an expert in excel

Can you make it a bit more more clear Brian?

Thanks once more

 

[Brian from Maui]

Thanks for all the replies and the time spending with my question

I just figured out that the initial formula suggested by Andrew does not work for my sample just because I impose the restriction ..."male" (at least in my file) In all other cases it works perfectly....so there should be a problem with the format.

As far as the formula suggested by Brian is concerned, I cannot make it work----how can I get a number if my formula starts with { e.g.

{=AVERAGE(IF((B2:B18="m")*(C2:C18>20),D2:D18))} ???????

When i copy that in a cell I get just the formula. When i put

={=AVERAGE(IF((B2:B18="m")*(C2:C18>20),D2:D18))} I get #VALUE

I am sure I am missing somethng here but i am not an expert in excel

Can you make it a bit more more clear Brian?

Thanks once more

Mine is an array formula entered by pressing CTRL+SHIFT+ENTER, the {} are entered automatically.

I used m as opposed to male because I was lazy, so Andrew's formula should also work. Andrew's formula is entered with just enter.

 

[cflorackis]

I just tried it Brian. With both formulas I am still getting 74.75!!!

I will get crazy!!!