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

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.

---
Per

"Paul Hood" skrev i meddelelsen
...
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

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" wrote in message
...
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.

---
Per

"Paul Hood" skrev i meddelelsen
...
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

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

"Paul Hood" skrev i meddelelsen
...
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" wrote in message
...
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.

---
Per

"Paul Hood" skrev i meddelelsen
...
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