if conditions in 2 cell correct then display third cell

[Paul Hood]

Hi all,
Sorry about the long subject.
I am trying to streamline a tracking spreadsheet that I created for our
school. Here's the problem.
A B C D
Child's name sex set data
Tom M T 1
Dick M B 2
Harry M T 2
Sue F T 1
Jane F B 2
and so on.
I want to check how a particular set of children are doing. So I want to be
able for example check the average score for all of the girls(F) in the
top(T) set, or the boys(M) in the bottom(B) set.
I solved the problem originally by in column E having this formula
=IF(AND(B2="F",C2="T")=TRUE,D2,"") which worked well once copied down, I
could then average the data, which is the final result I want. What I would
like to know is, can I do this all in one formula, check column B when
column C and when give an average? I have tried the following
{=AVERAGE(IF(AND(sex="F",set="T")=TRUE,y2m,""))} where column B is named
"sex" and column c is named "set" and column D is "y2m". It doesn't work and
I don't seem to be able to ask the help the right question. So you're my
only hope. If I can't solve it neatly then the old way will suffice. But my
sanity might suffer as I will keep thinking of a way to do it
Yours Paul

 

[Per Jessen]

Hi Paul

With the sex and set which you want to calculate average for in F2:G2, the
average can be calculated in H2 with this formula (in one line):

=SUMPRODUCT(--($B$2:$B$100=F2),--($C$2:$C$100=G2),--($D$2:$D$100))/SUMPRODUCT(--($B$2:$B$100=F2),--($C$2:$C$100=G2))

The formula can be copied down to set up an average table.

Hopes this help.

 

[Paul Hood]

Dear Per
Thanks that works wonderfully, as long as the data column has a value in it.
I can adjust the range manually but (and I'm pushing it here ) is there
any way it can cope with a blank cell (all of the cell have a formula in
them to generate the data)?
If not what you have done is brilliant and I can use it.
Thanks Paul

 

[Per Jessen]

Hi Paul

Thanks for your reply.

With this little modification, the divisor will only count non blank data
cells meeting previous conditons.

=SUMPRODUCT(--($B$2:$B$100=F2),--($C$2:$C$100=G2),--($D$2:$D$100))/SUMPRODUCT(--($B$2:$B$100=F2),--($C$2:$C$100=G2),--($D2:$D100<>""))

Regards,
Per