COUNTIF Question
 

Hi, I searched for my question in these forums, but no one answered it
throughly.

I work for a high school and I have an excel file with grade data for each
student. I want to find out what is the average grade for the male population
and the average grade for the female population for each subject.

1A B C D E
2Jones M 75 86 78
3James M 88 90 67
4Jared F 77 80 85
5John F 65 55 60

B=gender; C=English Grade; D=Math Grade; E=Science Grade

I want average of the Males and then i want to compare that to the averages
of Females. I tried several variations of the countif and daverage formulas,
but i get the #value! result.

End result would be to see in one cell how many males passed each course and
another cell to show their average. and then below that see how many females
passed the same courses.

Thanks in advance for everyone's help.

JCARLOS

COUNTIF Question
 

Try this:

A7 = Averages
B7 = M
B8 = F

Enter this formula in C7:

=SUMIF($B$2:$B$5,$B7,C$2:C$5)/COUNTIF($B$2:$B$5,$B7)

Copy across to E7 then down to C8:E8


--
Biff
Microsoft Excel MVP


"JCarlos" wrote in message
...
Hi, I searched for my question in these forums, but no one answered it
throughly.

I work for a high school and I have an excel file with grade data for each
student. I want to find out what is the average grade for the male
population
and the average grade for the female population for each subject.

1A B C D E
2Jones M 75 86 78
3James M 88 90 67
4Jared F 77 80 85
5John F 65 55 60

B=gender; C=English Grade; D=Math Grade; E=Science Grade

I want average of the Males and then i want to compare that to the
averages
of Females. I tried several variations of the countif and daverage
formulas,
but i get the #value! result.

End result would be to see in one cell how many males passed each course
and
another cell to show their average. and then below that see how many
females
passed the same courses.

Thanks in advance for everyone's help.

JCARLOS

COUNTIF Question
 

Thanks for the quick response. I tried the formula in the file but i get
#div/0 error. What can i try next?

JCarlos

"T. Valko" wrote:

Try this:

A7 = Averages
B7 = M
B8 = F

Enter this formula in C7:

=SUMIF($B$2:$B$5,$B7,C$2:C$5)/COUNTIF($B$2:$B$5,$B7)

Copy across to E7 then down to C8:E8


--
Biff
Microsoft Excel MVP


"JCarlos" wrote in message
...
Hi, I searched for my question in these forums, but no one answered it
throughly.

I work for a high school and I have an excel file with grade data for each
student. I want to find out what is the average grade for the male
population
and the average grade for the female population for each subject.

1A B C D E
2Jones M 75 86 78
3James M 88 90 67
4Jared F 77 80 85
5John F 65 55 60

B=gender; C=English Grade; D=Math Grade; E=Science Grade

I want average of the Males and then i want to compare that to the
averages
of Females. I tried several variations of the countif and daverage
formulas,
but i get the #value! result.

End result would be to see in one cell how many males passed each course
and
another cell to show their average. and then below that see how many
females
passed the same courses.

Thanks in advance for everyone's help.

JCARLOS

COUNTIF Question
 

=if(countif($b$2:$b$5,$b7)=0,"No entries",
SUMIF($B$2:$B$5,$B7,C$2:C$5)/COUNTIF($B$2:$B$5,$B7))



JCarlos wrote:

Thanks for the quick response. I tried the formula in the file but i get
#div/0 error. What can i try next?

JCarlos

"T. Valko" wrote:

Try this:

A7 = Averages
B7 = M
B8 = F

Enter this formula in C7:

=SUMIF($B$2:$B$5,$B7,C$2:C$5)/COUNTIF($B$2:$B$5,$B7)

Copy across to E7 then down to C8:E8


--
Biff
Microsoft Excel MVP


"JCarlos" wrote in message
...
Hi, I searched for my question in these forums, but no one answered it
throughly.

I work for a high school and I have an excel file with grade data for each
student. I want to find out what is the average grade for the male
population
and the average grade for the female population for each subject.

1A B C D E
2Jones M 75 86 78
3James M 88 90 67
4Jared F 77 80 85
5John F 65 55 60

B=gender; C=English Grade; D=Math Grade; E=Science Grade

I want average of the Males and then i want to compare that to the
averages
of Females. I tried several variations of the countif and daverage
formulas,
but i get the #value! result.

End result would be to see in one cell how many males passed each course
and
another cell to show their average. and then below that see how many
females
passed the same courses.

Thanks in advance for everyone's help.

JCARLOS




--

Dave Peterson

COUNTIF Question
 

i get #div/0 error

That's a "divide by 0" error. That would mean this part of the formula is
evaluating to 0:

COUNTIF($B$2:$B$5,$B7)

If that evaluates to 0 it means it's not counting any Ms or Fs.

Make sure the formula is referencing the correct range. Make sure the
entries are in fact M and F. Make sure there are no unseen characters in the
referenced range. Like this:

<spaceM
M<space
<spaceF<space

--
Biff
Microsoft Excel MVP


"JCarlos" wrote in message
...
Thanks for the quick response. I tried the formula in the file but i get
#div/0 error. What can i try next?

JCarlos

"T. Valko" wrote:

Try this:

A7 = Averages
B7 = M
B8 = F

Enter this formula in C7:

=SUMIF($B$2:$B$5,$B7,C$2:C$5)/COUNTIF($B$2:$B$5,$B7)

Copy across to E7 then down to C8:E8


--
Biff
Microsoft Excel MVP


"JCarlos" wrote in message
...
Hi, I searched for my question in these forums, but no one answered it
throughly.

I work for a high school and I have an excel file with grade data for
each
student. I want to find out what is the average grade for the male
population
and the average grade for the female population for each subject.

1A B C D E
2Jones M 75 86 78
3James M 88 90 67
4Jared F 77 80 85
5John F 65 55 60

B=gender; C=English Grade; D=Math Grade; E=Science Grade

I want average of the Males and then i want to compare that to the
averages
of Females. I tried several variations of the countif and daverage
formulas,
but i get the #value! result.

End result would be to see in one cell how many males passed each
course
and
another cell to show their average. and then below that see how many
females
passed the same courses.

Thanks in advance for everyone's help.

JCARLOS

COUNTIF Question
 

WOW. it is amazing what one small detail can affect a whole shut. my data
cell had extra spacing. And the numbers were being listed as text. Thanks so
much for your help!

JCarlos

"T. Valko" wrote:

i get #div/0 error

That's a "divide by 0" error. That would mean this part of the formula is
evaluating to 0:

COUNTIF($B$2:$B$5,$B7)

If that evaluates to 0 it means it's not counting any Ms or Fs.

Make sure the formula is referencing the correct range. Make sure the
entries are in fact M and F. Make sure there are no unseen characters in the
referenced range. Like this:

<spaceM
M<space
<spaceF<space

--
Biff
Microsoft Excel MVP


"JCarlos" wrote in message
...
Thanks for the quick response. I tried the formula in the file but i get
#div/0 error. What can i try next?

JCarlos

"T. Valko" wrote:

Try this:

A7 = Averages
B7 = M
B8 = F

Enter this formula in C7:

=SUMIF($B$2:$B$5,$B7,C$2:C$5)/COUNTIF($B$2:$B$5,$B7)

Copy across to E7 then down to C8:E8


--
Biff
Microsoft Excel MVP


"JCarlos" wrote in message
...
Hi, I searched for my question in these forums, but no one answered it
throughly.

I work for a high school and I have an excel file with grade data for
each
student. I want to find out what is the average grade for the male
population
and the average grade for the female population for each subject.

1A B C D E
2Jones M 75 86 78
3James M 88 90 67
4Jared F 77 80 85
5John F 65 55 60

B=gender; C=English Grade; D=Math Grade; E=Science Grade

I want average of the Males and then i want to compare that to the
averages
of Females. I tried several variations of the countif and daverage
formulas,
but i get the #value! result.

End result would be to see in one cell how many males passed each
course
and
another cell to show their average. and then below that see how many
females
passed the same courses.

Thanks in advance for everyone's help.

JCARLOS

COUNTIF Question
 

Glad you got it straightened out. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"JCarlos" wrote in message
...
WOW. it is amazing what one small detail can affect a whole shut. my data
cell had extra spacing. And the numbers were being listed as text. Thanks
so
much for your help!

JCarlos

"T. Valko" wrote:

i get #div/0 error

That's a "divide by 0" error. That would mean this part of the formula is
evaluating to 0:

COUNTIF($B$2:$B$5,$B7)

If that evaluates to 0 it means it's not counting any Ms or Fs.

Make sure the formula is referencing the correct range. Make sure the
entries are in fact M and F. Make sure there are no unseen characters in
the
referenced range. Like this:

<spaceM
M<space
<spaceF<space

--
Biff
Microsoft Excel MVP


"JCarlos" wrote in message
...
Thanks for the quick response. I tried the formula in the file but i
get
#div/0 error. What can i try next?

JCarlos

"T. Valko" wrote:

Try this:

A7 = Averages
B7 = M
B8 = F

Enter this formula in C7:

=SUMIF($B$2:$B$5,$B7,C$2:C$5)/COUNTIF($B$2:$B$5,$B7)

Copy across to E7 then down to C8:E8


--
Biff
Microsoft Excel MVP


"JCarlos" wrote in message
...
Hi, I searched for my question in these forums, but no one answered
it
throughly.

I work for a high school and I have an excel file with grade data
for
each
student. I want to find out what is the average grade for the male
population
and the average grade for the female population for each subject.

1A B C D E
2Jones M 75 86 78
3James M 88 90 67
4Jared F 77 80 85
5John F 65 55 60

B=gender; C=English Grade; D=Math Grade; E=Science Grade

I want average of the Males and then i want to compare that to the
averages
of Females. I tried several variations of the countif and daverage
formulas,
but i get the #value! result.

End result would be to see in one cell how many males passed each
course
and
another cell to show their average. and then below that see how many
females
passed the same courses.

Thanks in advance for everyone's help.

JCARLOS