# calculate averages

#### cflorackis

##### Board Regular
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

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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.

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?

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

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

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

A picture is worth....

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

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

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.

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

I will get crazy!!!

Replies
2
Views
234
Replies
6
Views
182
Replies
4
Views
450
Replies
2
Views
149
Replies
1
Views
844

1,203,605
Messages
6,056,262
Members
444,853
Latest member
sam69

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back