calculate averages

cflorackis

Board Regular
Joined
Aug 18, 2002
Messages
137
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
ABCDE
1persongenderageweight
21male2580
32female2658average weight of males >20 years old
43female3284average weight of males >30 years old
54female1478average weight of males >40 years old
65female1690average weight of males >50 years old
76male85125
87male80150
98female9288average weight of females >20 years old
109male1759average weight of females >30 years old
1110male4888average weight of females >40 years old
1211female2347average weight of females >50 years old
1312male2849
1413female45150
1514female7898
1615male8295
1716female1969
1817male5282
Sheet1
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

Board Regular
Joined
Aug 18, 2002
Messages
137
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?
 

cflorackis

Board Regular
Joined
Aug 18, 2002
Messages
137

ADVERTISEMENT

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

Well-known Member
Joined
May 25, 2004
Messages
1,218
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
:eek: :eek: :eek:
 

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,458

ADVERTISEMENT

A picture is worth....

:LOL:
Book1
BCDEF
2m258095.57143
3f265895.57143
4f328495.57143
5f1478
6f1690
7m85125
8m80150
9f9288
10f1759
11m4888
12f2347
13m2849
14f45150
15f7898
16m8295
17f1969
18m5282
Sheet1
 

cflorackis

Board Regular
Joined
Aug 18, 2002
Messages
137
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

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,458
cflorackis said:
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

Board Regular
Joined
Aug 18, 2002
Messages
137
I just tried it Brian. With both formulas I am still getting 74.75!!!

I will get crazy!!!
 

Watch MrExcel Video

Forum statistics

Threads
1,118,081
Messages
5,570,089
Members
412,310
Latest member
mark884
Top