filtering data for analysis
 

I have a spreadsheet of students grades across different subjects with
formula rows below to total grades by subject, then convert totals to
percentages. I need to do the same analysis for male and female students
separately, then produce a summary in a separate worksheet. Is there a way
to do this using an IF formula to keep the three analyses (All, F, M) on the
same sheet, so that I can then create links to show the summary on a
different sheet?

filtering data for analysis
 

Suppose you have M or F in column B, with column C used for the first
subject. Suppose you have 100 rows of data, then you can put this
formula below your data (eg in cell C102):

=SUMIF($B$1:$B$100,"M",C$1:C$100)

to give the total marks for males, and in C103:

=SUMIF($B$1:$B$100,"F",C$1:C$100)

will give the total marks for females. Copy these two formulae across
the row for as many subjects as you have.

Hope this helps.

Pete

On Sep 16, 9:44*am, JVS wrote:
I have a spreadsheet of students grades across different subjects with
formula rows below to total grades by subject, then convert totals to
percentages. *I need to do the same analysis for male and female students
separately, then produce a summary in a separate worksheet. *Is there a way
to do this using an IF formula to keep the three analyses (All, F, M) on the
same sheet, so that I can then create links to show the summary on a
different sheet? *

filtering data for analysis
 

Thanks very much, Pete, that's great!

"Pete_UK" wrote:

On Sep 17, 12:51 pm, Pete_UK wrote:
Okay, suppose you have this setup:

Name Gender Subject_1 Subject_2 etc

with headers in row 1 and grades of A, B, C etc shown below the
subjects. Your summary table would need to be in this format:

Gender Grade Subject_1 Subject_2 etc
F A
F B
F C
M A
M B
M C

and so on.

Sorry, I accidentally clicked the Send button by mistake. Carrying
on ...

Suppose the headings for this summary table are in row 102, then put
this in C103:

=SUMPRODUCT(--($B$2:$B$100=$A103),--(C$2:C$100=$B103))

You will need to adjust the range and cell references to suit your
data, but ensure that you have the $ signs as shown. Then you can copy
the formula across the row to cover the number of subjects that you
have, and then you can copy those formulae down as required.

Hope this helps.

Pete


On Sep 17, 11:44 am, JVS wrote:



Thanks, Pete, however I think my original description may have been
misleading. It may have been clearer if I had said that I need not to
"total" grades but to count the number of each grade ie how many girls scored
grade A, how many grade B etc. I have tried to adapt your formula by using
COUNTIF instead os SUMIF, but I can't get it to work yet. Any suggestions
gratefully received. Thanks again!

JVS

"Pete_UK" wrote:
Suppose you have M or F in column B, with column C used for the first
subject. Suppose you have 100 rows of data, then you can put this
formula below your data (eg in cell C102):

=SUMIF($B$1:$B$100,"M",C$1:C$100)

to give the total marks for males, and in C103:

=SUMIF($B$1:$B$100,"F",C$1:C$100)

will give the total marks for females. Copy these two formulae across
the row for as many subjects as you have.

Hope this helps.

Pete

On Sep 16, 9:44 am, JVS wrote:
I have a spreadsheet of students grades across different subjects with
formula rows below to total grades by subject, then convert totals to
percentages. I need to do the same analysis for male and female students
separately, then produce a summary in a separate worksheet. Is there a way
to do this using an IF formula to keep the three analyses (All, F, M) on the
same sheet, so that I can then create links to show the summary on a
different sheet? - Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

filtering data for analysis
 

You're welcome - thanks for feeding back.

Pete

On Sep 17, 10:23*pm, JVS wrote:
Thanks very much, Pete, that's great!



"Pete_UK" wrote:
On Sep 17, 12:51 pm, Pete_UK wrote:
Okay, suppose you have this setup:

Name * * *Gender * * Subject_1 * * Subject_2 * *etc

with headers in row 1 and grades of A, B, C etc shown below the
subjects. Your summary table would need to be in this format:

Gender * * Grade * * Subject_1 * *Subject_2 * etc
* * F * * * * * * *A
* * F * * * * * * *B
* * F * * * * * * *C
* * M * * * * * * *A
* * M * * * * * * *B
* * M * * * * * * *C

and so on.

Sorry, I accidentally clicked the Send button by mistake. Carrying
on ...

Suppose the headings for this summary table are in row 102, then put
this in C103:

=SUMPRODUCT(--($B$2:$B$100=$A103),--(C$2:C$100=$B103))

You will need to adjust the range and cell references to suit your
data, but ensure that you have the $ signs as shown. Then you can copy
the formula across the row to cover the number of subjects that you
have, and then you can copy those formulae down as required.

Hope this helps.

Pete

On Sep 17, 11:44 am, JVS wrote:

Thanks, Pete, however I think my original description may have been
misleading. *It may have been clearer if I had said that I need not to
"total" grades but to count the number of each grade ie how many girls scored
grade A, how many grade B etc. *I have tried to adapt your formula by using
COUNTIF instead os SUMIF, but I can't get it to work yet. *Any suggestions
gratefully received. *Thanks again!

JVS

"Pete_UK" wrote:
Suppose you have M or F in column B, with column C used for the first
subject. Suppose you have 100 rows of data, then you can put this
formula below your data (eg in cell C102):

=SUMIF($B$1:$B$100,"M",C$1:C$100)

to give the total marks for males, and in C103:

=SUMIF($B$1:$B$100,"F",C$1:C$100)

will give the total marks for females. Copy these two formulae across
the row for as many subjects as you have.

Hope this helps.

Pete

On Sep 16, 9:44 am, JVS wrote:
I have a spreadsheet of students grades across different subjects with
formula rows below to total grades by subject, then convert totals to
percentages. *I need to do the same analysis for male and female students
separately, then produce a summary in a separate worksheet. *Is there a way
to do this using an IF formula to keep the three analyses (All, F, M) on the
same sheet, so that I can then create links to show the summary on a
different sheet? *- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -