Counting number of grades in a row

[Marie1uk]

Hi there,

I am a teacher and need to analyse my results for review and target setting purposes.

I need the spreadsheet to generate results for the following:

Number of pupils scoring 5 grades (or higher) A* - C
% of pupils scoring 5 grades (or higher) A* - C
Number of pupils scoring 5 grades (or higher) A* - G
% of pupils scoring 5 grades (or higher) A* - G
Number of pupils scoring 1 (or higher) grade A*-G
% of pupils scoring 1 grade (or higher) grade A* - G
Number & % of pupils who were absent

In column A will be the pupil's first name, column B will be their surname, C their gender and in columns D - O will be where I will input their grades (not all cells D - O will necessarily be used). Using this format what is the best formula for obtaining the necessary results?

Many thanks for your consideration,

Marie.

[Marie1uk]

Any help greatly appreciated.

[Domenic]

Hi Marie!

Assuming that D2:O10 contains your grades...

For the number of pupils scoring 5 grades or higheer (A*- C):

Q1:

=SUM(--(MMULT((D2:O10="A*")+(D2:O10="A")+(D2:O10="B")+(D2:O10="C"),TRANSPOSE(COLUMN(D2:O10)*0+1))>=5))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER. For the percentage of pupils scoring 5 grades or higher (A*- C):

R1:

=Q1/COUNTA(A2:A10)

Adjust the range and criteria accordingly. As far as the number and percentage of pupils absent, can you provide more information?

Hi there,

I am a teacher and need to analyse my results for review and target setting purposes.

I need the spreadsheet to generate results for the following:

Number of pupils scoring 5 grades (or higher) A* - C
% of pupils scoring 5 grades (or higher) A* - C
Number of pupils scoring 5 grades (or higher) A* - G
% of pupils scoring 5 grades (or higher) A* - G
Number of pupils scoring 1 (or higher) grade A*-G
% of pupils scoring 1 grade (or higher) grade A* - G
Number & % of pupils who were absent

In column A will be the pupil's first name, column B will be their surname, C their gender and in columns D - O will be where I will input their grades (not all cells D - O will necessarily be used). Using this format what is the best formula for obtaining the necessary results?

Many thanks for your consideration,

Marie.

[Lewis Clark]

I'm not sure I understand what you are asking for. Do the grades range from
A to G? In the first 2 lines, are you looking for the number of students
who earned at least 5 grades of C or higher? If a pupil is absent, will the
cell for that grade be blank?

"Marie1uk" <[email protected]> wrote in
message news:[email protected]...
>
> Hi there,
>
> I am a teacher and need to analyse my results for review and target
> setting purposes.
>
> I need the spreadsheet to generate results for the following:
>
> Number of pupils scoring 5 grades (or higher) A* - C
> % of pupils scoring 5 grades (or higher) A* - C
> Number of pupils scoring 5 grades (or higher) A* - G
> % of pupils scoring 5 grades (or higher) A* - G
> Number of pupils scoring 1 (or higher) grade A*-G
> % of pupils scoring 1 grade (or higher) grade A* - G
> Number & % of pupils who were absent
>
> In column A will be the pupil's first name, column B will be their
> surname, C their gender and in columns D - O will be where I will input
> their grades (not all cells D - O will necessarily be used). Using this
> format what is the best formula for obtaining the necessary results?
>
> Many thanks for your consideration,
>
> Marie.
>
>
> --
> Marie1uk
> ------------------------------------------------------------------------
> Marie1uk's Profile:
> http://www.excelforum.com/member.php...o&userid=20984
> View this thread: http://www.excelforum.com/showthread...hreadid=384303
>

[Marie1uk]

Grades range from A* (highest) to U (lowest), but U's are not needed as part of the analysis I need, just A*, A, B, C,D,E,F & G. Pupils who fail to turn up for the exam are classed as Abs on the spreadsheet, where Abs will replace the 'grade' for that exam. The formula then needs to count how many Abs marks there were for all pupils (305 ish) and express them as a % of all grades possible, so:

305 pupils take for example 8 exams each = 2420 grades. 20 exams are missed by pupils failing to turn up and are classed as Abs on the spreadsheet so it needs to be able to count those 20 cells with abs in as express it as a % of all grades possible, ie 20/2040. Hope this makes sense

[Domenic]

For the total number of 'Abs'...

=COUNTIF(Range,"Abs")

For the total number of 'Abs' as a percentage of all grades possible...

=COUNTIF(Range,"Abs")/2440

..and format as 'Percentage'.

Hope this helps!

Grades range from A* (highest) to U (lowest), but U's are not needed as part of the analysis I need, just A*, A, B, C,D,E,F & G. Pupils who fail to turn up for the exam are classed as Abs on the spreadsheet, where Abs will replace the 'grade' for that exam. The formula then needs to count how many Abs marks there were for all pupils (305 ish) and express them as a % of all grades possible, so:

305 pupils take for example 8 exams each = 2420 grades. 20 exams are missed by pupils failing to turn up and are classed as Abs on the spreadsheet so it needs to be able to count those 20 cells with abs in as express it as a % of all grades possible, ie 20/2040. Hope this makes sense

[Marie1uk]

The 2440 was a hypothetical example to illustrate what I wanted, not the exact number of grades that the spreadsheet will contain - sorry for the confusion. The spreadsheet needs to count all cells that have abs in them in a specified range and then divide by those cells that contain A*, A, B, C, D, E, F, G, U & Abs to arrive at a % of abs marks out of all possible grades.

[Domenic]

Try the following instead...

=COUNTIF(D2:O10,"Abs")/SUMPRODUCT(--(ISNUMBER(MATCH(D2:O10,{"A","B","C","D","E","F","G","U","Abs"},0))))

OR

=COUNTIF(D2:O10,"Abs")/MAX(SUMPRODUCT(--(ISNUMBER(MATCH(D2:O10,{"A","B","C","D","E","F","G","U","Abs"},0)))),1)

...which will return a 0 instead of a #DIV/0 error when the denominator evaluates to 0.

Hope this helps!

The 2440 was a hypothetical example to illustrate what I wanted, not the exact number of grades that the spreadsheet will contain - sorry for the confusion. The spreadsheet needs to count all cells that have abs in them in a specified range and then divide by those cells that contain A*, A, B, C, D, E, F, G, U & Abs to arrive at a % of abs marks out of all possible grades.

[Aladin Akyurek]

Maybe:

=COUNTIF(GradeRange,"Abs")/COUNTIF(GradeRange,"*?")

Marie1uk wrote:
> The 2440 was a hypothetical example to illustrate what I wanted, not the
> exact number of grades that the spreadsheet will contain - sorry for the
> confusion. The spreadsheet needs to count all cells that have abs in
> them in a specified range and then divide by those cells that contain
> A*, A, B, C, D, E, F, G, U & Abs to arrive at a % of abs marks out of
> all possible grades.
>
>

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

[Marie1uk]

Ok thanks,

I have decided to include a by gender 5 or more A*-U & 1 or more A*-U analysis too and set up a test area of 4 pupils marks with data:

In A24 will be their first name, B24 will be their surname, C24 their gender (M=male, F=Female) and from D24 - M24 will be a list of their grades (A*- U). D24 - M27 will hold the test grades.

I need it to calculate a BOYS & GIRLS ONLY analysis of pupils getting 5 or more A*-U and 1 or more A*-U (number of boys / girls as well as a percentage of boys / girls).

Thanks in advance,

Marie.

[Marie1uk]

Any help much appreciated.

[Domenic]

O24:

=SUM(--(MMULT((C24:C27="M")*ISNUMBER(MATCH(D24:M27,{"A","B","C","D","E","F","G","U"},0)),TRANSPOSE(COLUMN(D24:M27)*0+1))>=5))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

P24:

=O24/COUNTIF(C24:C27,"M")

For '1 or more A*-U', change '>=5' to '>=1'.

Hope this helps!

Ok thanks,

I have decided to include a by gender 5 or more A*-U & 1 or more A*-U analysis too and set up a test area of 4 pupils marks with data:

In A24 will be their first name, B24 will be their surname, C24 their gender (M=male, F=Female) and from D24 - M24 will be a list of their grades (A*- U). D24 - M27 will hold the test grades.

I need it to calculate a BOYS & GIRLS ONLY analysis of pupils getting 5 or more A*-U and 1 or more A*-U (number of boys / girls as well as a percentage of boys / girls).

Thanks in advance,

Marie.

[Lewis Clark]

Domenic,

I've studied your replies to this thread with great interest. This use of
matrix math is very clever. Thank you for the lesson!

Lewis

[Domenic]

Domenic,

I've studied your replies to this thread with great interest. This use of
matrix math is very clever. Thank you for the lesson!

Lewis

You're very welcome! I too find it very interesting.

Cheers!

[Lewis Clark]

I'm not sure I understand what you are asking for. Do the grades range from
A to G? In the first 2 lines, are you looking for the number of students
who earned at least 5 grades of C or higher? If a pupil is absent, will the
cell for that grade be blank?

"Marie1uk" <[email protected]> wrote in
message news:[email protected]...
>
> Hi there,
>
> I am a teacher and need to analyse my results for review and target
> setting purposes.
>
> I need the spreadsheet to generate results for the following:
>
> Number of pupils scoring 5 grades (or higher) A* - C
> % of pupils scoring 5 grades (or higher) A* - C
> Number of pupils scoring 5 grades (or higher) A* - G
> % of pupils scoring 5 grades (or higher) A* - G
> Number of pupils scoring 1 (or higher) grade A*-G
> % of pupils scoring 1 grade (or higher) grade A* - G
> Number & % of pupils who were absent
>
> In column A will be the pupil's first name, column B will be their
> surname, C their gender and in columns D - O will be where I will input
> their grades (not all cells D - O will necessarily be used). Using this
> format what is the best formula for obtaining the necessary results?
>
> Many thanks for your consideration,
>
> Marie.
>
>
> --
> Marie1uk
> ------------------------------------------------------------------------
> Marie1uk's Profile:
> http://www.excelforum.com/member.php...o&userid=20984
> View this thread: http://www.excelforum.com/showthread...hreadid=384303
>

[Aladin Akyurek]

Maybe:

=COUNTIF(GradeRange,"Abs")/COUNTIF(GradeRange,"*?")

Marie1uk wrote:
> The 2440 was a hypothetical example to illustrate what I wanted, not the
> exact number of grades that the spreadsheet will contain - sorry for the
> confusion. The spreadsheet needs to count all cells that have abs in
> them in a specified range and then divide by those cells that contain
> A*, A, B, C, D, E, F, G, U & Abs to arrive at a % of abs marks out of
> all possible grades.
>
>

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

[Lewis Clark]

Domenic,

I've studied your replies to this thread with great interest. This use of
matrix math is very clever. Thank you for the lesson!

Lewis

[Lewis Clark]

I'm not sure I understand what you are asking for. Do the grades range from
A to G? In the first 2 lines, are you looking for the number of students
who earned at least 5 grades of C or higher? If a pupil is absent, will the
cell for that grade be blank?

"Marie1uk" <[email protected]> wrote in
message news:[email protected]...
>
> Hi there,
>
> I am a teacher and need to analyse my results for review and target
> setting purposes.
>
> I need the spreadsheet to generate results for the following:
>
> Number of pupils scoring 5 grades (or higher) A* - C
> % of pupils scoring 5 grades (or higher) A* - C
> Number of pupils scoring 5 grades (or higher) A* - G
> % of pupils scoring 5 grades (or higher) A* - G
> Number of pupils scoring 1 (or higher) grade A*-G
> % of pupils scoring 1 grade (or higher) grade A* - G
> Number & % of pupils who were absent
>
> In column A will be the pupil's first name, column B will be their
> surname, C their gender and in columns D - O will be where I will input
> their grades (not all cells D - O will necessarily be used). Using this
> format what is the best formula for obtaining the necessary results?
>
> Many thanks for your consideration,
>
> Marie.
>
>
> --
> Marie1uk
> ------------------------------------------------------------------------
> Marie1uk's Profile:
> http://www.excelforum.com/member.php...o&userid=20984
> View this thread: http://www.excelforum.com/showthread...hreadid=384303
>

[Aladin Akyurek]

Maybe:

=COUNTIF(GradeRange,"Abs")/COUNTIF(GradeRange,"*?")

Marie1uk wrote:
> The 2440 was a hypothetical example to illustrate what I wanted, not the
> exact number of grades that the spreadsheet will contain - sorry for the
> confusion. The spreadsheet needs to count all cells that have abs in
> them in a specified range and then divide by those cells that contain
> A*, A, B, C, D, E, F, G, U & Abs to arrive at a % of abs marks out of
> all possible grades.
>
>

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

[Lewis Clark]

Domenic,

I've studied your replies to this thread with great interest. This use of
matrix math is very clever. Thank you for the lesson!

Lewis

[Lewis Clark]

Domenic,

I've studied your replies to this thread with great interest. This use of
matrix math is very clever. Thank you for the lesson!

Lewis

[Aladin Akyurek]

Maybe:

=COUNTIF(GradeRange,"Abs")/COUNTIF(GradeRange,"*?")

Marie1uk wrote:
> The 2440 was a hypothetical example to illustrate what I wanted, not the
> exact number of grades that the spreadsheet will contain - sorry for the
> confusion. The spreadsheet needs to count all cells that have abs in
> them in a specified range and then divide by those cells that contain
> A*, A, B, C, D, E, F, G, U & Abs to arrive at a % of abs marks out of
> all possible grades.
>
>

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

[Lewis Clark]

I'm not sure I understand what you are asking for. Do the grades range from
A to G? In the first 2 lines, are you looking for the number of students
who earned at least 5 grades of C or higher? If a pupil is absent, will the
cell for that grade be blank?

"Marie1uk" <[email protected]> wrote in
message news:[email protected]...
>
> Hi there,
>
> I am a teacher and need to analyse my results for review and target
> setting purposes.
>
> I need the spreadsheet to generate results for the following:
>
> Number of pupils scoring 5 grades (or higher) A* - C
> % of pupils scoring 5 grades (or higher) A* - C
> Number of pupils scoring 5 grades (or higher) A* - G
> % of pupils scoring 5 grades (or higher) A* - G
> Number of pupils scoring 1 (or higher) grade A*-G
> % of pupils scoring 1 grade (or higher) grade A* - G
> Number & % of pupils who were absent
>
> In column A will be the pupil's first name, column B will be their
> surname, C their gender and in columns D - O will be where I will input
> their grades (not all cells D - O will necessarily be used). Using this
> format what is the best formula for obtaining the necessary results?
>
> Many thanks for your consideration,
>
> Marie.
>
>
> --
> Marie1uk
> ------------------------------------------------------------------------
> Marie1uk's Profile:
> http://www.excelforum.com/member.php...o&userid=20984
> View this thread: http://www.excelforum.com/showthread...hreadid=384303
>

[Lewis Clark]

I'm not sure I understand what you are asking for. Do the grades range from
A to G? In the first 2 lines, are you looking for the number of students
who earned at least 5 grades of C or higher? If a pupil is absent, will the
cell for that grade be blank?

"Marie1uk" <[email protected]> wrote in
message news:[email protected]...
>
> Hi there,
>
> I am a teacher and need to analyse my results for review and target
> setting purposes.
>
> I need the spreadsheet to generate results for the following:
>
> Number of pupils scoring 5 grades (or higher) A* - C
> % of pupils scoring 5 grades (or higher) A* - C
> Number of pupils scoring 5 grades (or higher) A* - G
> % of pupils scoring 5 grades (or higher) A* - G
> Number of pupils scoring 1 (or higher) grade A*-G
> % of pupils scoring 1 grade (or higher) grade A* - G
> Number & % of pupils who were absent
>
> In column A will be the pupil's first name, column B will be their
> surname, C their gender and in columns D - O will be where I will input
> their grades (not all cells D - O will necessarily be used). Using this
> format what is the best formula for obtaining the necessary results?
>
> Many thanks for your consideration,
>
> Marie.
>
>
> --
> Marie1uk
> ------------------------------------------------------------------------
> Marie1uk's Profile:
> http://www.excelforum.com/member.php...o&userid=20984
> View this thread: http://www.excelforum.com/showthread...hreadid=384303
>

[Aladin Akyurek]

Maybe:

=COUNTIF(GradeRange,"Abs")/COUNTIF(GradeRange,"*?")

Marie1uk wrote:
> The 2440 was a hypothetical example to illustrate what I wanted, not the
> exact number of grades that the spreadsheet will contain - sorry for the
> confusion. The spreadsheet needs to count all cells that have abs in
> them in a specified range and then divide by those cells that contain
> A*, A, B, C, D, E, F, G, U & Abs to arrive at a % of abs marks out of
> all possible grades.
>
>

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

[Lewis Clark]

Domenic,

I've studied your replies to this thread with great interest. This use of
matrix math is very clever. Thank you for the lesson!

Lewis

[Lewis Clark]

Domenic,

I've studied your replies to this thread with great interest. This use of
matrix math is very clever. Thank you for the lesson!

Lewis

[Aladin Akyurek]

Maybe:

=COUNTIF(GradeRange,"Abs")/COUNTIF(GradeRange,"*?")

Marie1uk wrote:
> The 2440 was a hypothetical example to illustrate what I wanted, not the
> exact number of grades that the spreadsheet will contain - sorry for the
> confusion. The spreadsheet needs to count all cells that have abs in
> them in a specified range and then divide by those cells that contain
> A*, A, B, C, D, E, F, G, U & Abs to arrive at a % of abs marks out of
> all possible grades.
>
>

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

[Lewis Clark]

I'm not sure I understand what you are asking for. Do the grades range from
A to G? In the first 2 lines, are you looking for the number of students
who earned at least 5 grades of C or higher? If a pupil is absent, will the
cell for that grade be blank?

"Marie1uk" <[email protected]> wrote in
message news:[email protected]...
>
> Hi there,
>
> I am a teacher and need to analyse my results for review and target
> setting purposes.
>
> I need the spreadsheet to generate results for the following:
>
> Number of pupils scoring 5 grades (or higher) A* - C
> % of pupils scoring 5 grades (or higher) A* - C
> Number of pupils scoring 5 grades (or higher) A* - G
> % of pupils scoring 5 grades (or higher) A* - G
> Number of pupils scoring 1 (or higher) grade A*-G
> % of pupils scoring 1 grade (or higher) grade A* - G
> Number & % of pupils who were absent
>
> In column A will be the pupil's first name, column B will be their
> surname, C their gender and in columns D - O will be where I will input
> their grades (not all cells D - O will necessarily be used). Using this
> format what is the best formula for obtaining the necessary results?
>
> Many thanks for your consideration,
>
> Marie.
>
>
> --
> Marie1uk
> ------------------------------------------------------------------------
> Marie1uk's Profile:
> http://www.excelforum.com/member.php...o&userid=20984
> View this thread: http://www.excelforum.com/showthread...hreadid=384303
>

[Lewis Clark]

Domenic,

I've studied your replies to this thread with great interest. This use of
matrix math is very clever. Thank you for the lesson!

Lewis

[Aladin Akyurek]

Maybe:

=COUNTIF(GradeRange,"Abs")/COUNTIF(GradeRange,"*?")

Marie1uk wrote:
> The 2440 was a hypothetical example to illustrate what I wanted, not the
> exact number of grades that the spreadsheet will contain - sorry for the
> confusion. The spreadsheet needs to count all cells that have abs in
> them in a specified range and then divide by those cells that contain
> A*, A, B, C, D, E, F, G, U & Abs to arrive at a % of abs marks out of
> all possible grades.
>
>

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

[Lewis Clark]

I'm not sure I understand what you are asking for. Do the grades range from
A to G? In the first 2 lines, are you looking for the number of students
who earned at least 5 grades of C or higher? If a pupil is absent, will the
cell for that grade be blank?

"Marie1uk" <[email protected]> wrote in
message news:[email protected]...
>
> Hi there,
>
> I am a teacher and need to analyse my results for review and target
> setting purposes.
>
> I need the spreadsheet to generate results for the following:
>
> Number of pupils scoring 5 grades (or higher) A* - C
> % of pupils scoring 5 grades (or higher) A* - C
> Number of pupils scoring 5 grades (or higher) A* - G
> % of pupils scoring 5 grades (or higher) A* - G
> Number of pupils scoring 1 (or higher) grade A*-G
> % of pupils scoring 1 grade (or higher) grade A* - G
> Number & % of pupils who were absent
>
> In column A will be the pupil's first name, column B will be their
> surname, C their gender and in columns D - O will be where I will input
> their grades (not all cells D - O will necessarily be used). Using this
> format what is the best formula for obtaining the necessary results?
>
> Many thanks for your consideration,
>
> Marie.
>
>
> --
> Marie1uk
> ------------------------------------------------------------------------
> Marie1uk's Profile:
> http://www.excelforum.com/member.php...o&userid=20984
> View this thread: http://www.excelforum.com/showthread...hreadid=384303
>

[Lewis Clark]

Domenic,

I've studied your replies to this thread with great interest. This use of
matrix math is very clever. Thank you for the lesson!

Lewis

[Aladin Akyurek]

Maybe:

=COUNTIF(GradeRange,"Abs")/COUNTIF(GradeRange,"*?")

Marie1uk wrote:
> The 2440 was a hypothetical example to illustrate what I wanted, not the
> exact number of grades that the spreadsheet will contain - sorry for the
> confusion. The spreadsheet needs to count all cells that have abs in
> them in a specified range and then divide by those cells that contain
> A*, A, B, C, D, E, F, G, U & Abs to arrive at a % of abs marks out of
> all possible grades.
>
>

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

[Lewis Clark]

I'm not sure I understand what you are asking for. Do the grades range from
A to G? In the first 2 lines, are you looking for the number of students
who earned at least 5 grades of C or higher? If a pupil is absent, will the
cell for that grade be blank?

"Marie1uk" <[email protected]> wrote in
message news:[email protected]...
>
> Hi there,
>
> I am a teacher and need to analyse my results for review and target
> setting purposes.
>
> I need the spreadsheet to generate results for the following:
>
> Number of pupils scoring 5 grades (or higher) A* - C
> % of pupils scoring 5 grades (or higher) A* - C
> Number of pupils scoring 5 grades (or higher) A* - G
> % of pupils scoring 5 grades (or higher) A* - G
> Number of pupils scoring 1 (or higher) grade A*-G
> % of pupils scoring 1 grade (or higher) grade A* - G
> Number & % of pupils who were absent
>
> In column A will be the pupil's first name, column B will be their
> surname, C their gender and in columns D - O will be where I will input
> their grades (not all cells D - O will necessarily be used). Using this
> format what is the best formula for obtaining the necessary results?
>
> Many thanks for your consideration,
>
> Marie.
>
>
> --
> Marie1uk
> ------------------------------------------------------------------------
> Marie1uk's Profile:
> http://www.excelforum.com/member.php...o&userid=20984
> View this thread: http://www.excelforum.com/showthread...hreadid=384303
>

[Lewis Clark]

I'm not sure I understand what you are asking for. Do the grades range from
A to G? In the first 2 lines, are you looking for the number of students
who earned at least 5 grades of C or higher? If a pupil is absent, will the
cell for that grade be blank?

"Marie1uk" <[email protected]> wrote in
message news:[email protected]...
>
> Hi there,
>
> I am a teacher and need to analyse my results for review and target
> setting purposes.
>
> I need the spreadsheet to generate results for the following:
>
> Number of pupils scoring 5 grades (or higher) A* - C
> % of pupils scoring 5 grades (or higher) A* - C
> Number of pupils scoring 5 grades (or higher) A* - G
> % of pupils scoring 5 grades (or higher) A* - G
> Number of pupils scoring 1 (or higher) grade A*-G
> % of pupils scoring 1 grade (or higher) grade A* - G
> Number & % of pupils who were absent
>
> In column A will be the pupil's first name, column B will be their
> surname, C their gender and in columns D - O will be where I will input
> their grades (not all cells D - O will necessarily be used). Using this
> format what is the best formula for obtaining the necessary results?
>
> Many thanks for your consideration,
>
> Marie.
>
>
> --
> Marie1uk
> ------------------------------------------------------------------------
> Marie1uk's Profile:
> http://www.excelforum.com/member.php...o&userid=20984
> View this thread: http://www.excelforum.com/showthread...hreadid=384303
>

[Lewis Clark]

Domenic,

I've studied your replies to this thread with great interest. This use of
matrix math is very clever. Thank you for the lesson!

Lewis

[Aladin Akyurek]

Maybe:

=COUNTIF(GradeRange,"Abs")/COUNTIF(GradeRange,"*?")

Marie1uk wrote:
> The 2440 was a hypothetical example to illustrate what I wanted, not the
> exact number of grades that the spreadsheet will contain - sorry for the
> confusion. The spreadsheet needs to count all cells that have abs in
> them in a specified range and then divide by those cells that contain
> A*, A, B, C, D, E, F, G, U & Abs to arrive at a % of abs marks out of
> all possible grades.
>
>

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

[Lewis Clark]

Domenic,

I've studied your replies to this thread with great interest. This use of
matrix math is very clever. Thank you for the lesson!

Lewis

[Aladin Akyurek]

Maybe:

=COUNTIF(GradeRange,"Abs")/COUNTIF(GradeRange,"*?")

Marie1uk wrote:
> The 2440 was a hypothetical example to illustrate what I wanted, not the
> exact number of grades that the spreadsheet will contain - sorry for the
> confusion. The spreadsheet needs to count all cells that have abs in
> them in a specified range and then divide by those cells that contain
> A*, A, B, C, D, E, F, G, U & Abs to arrive at a % of abs marks out of
> all possible grades.
>
>

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

[Lewis Clark]

I'm not sure I understand what you are asking for. Do the grades range from
A to G? In the first 2 lines, are you looking for the number of students
who earned at least 5 grades of C or higher? If a pupil is absent, will the
cell for that grade be blank?

"Marie1uk" <[email protected]> wrote in
message news:[email protected]...
>
> Hi there,
>
> I am a teacher and need to analyse my results for review and target
> setting purposes.
>
> I need the spreadsheet to generate results for the following:
>
> Number of pupils scoring 5 grades (or higher) A* - C
> % of pupils scoring 5 grades (or higher) A* - C
> Number of pupils scoring 5 grades (or higher) A* - G
> % of pupils scoring 5 grades (or higher) A* - G
> Number of pupils scoring 1 (or higher) grade A*-G
> % of pupils scoring 1 grade (or higher) grade A* - G
> Number & % of pupils who were absent
>
> In column A will be the pupil's first name, column B will be their
> surname, C their gender and in columns D - O will be where I will input
> their grades (not all cells D - O will necessarily be used). Using this
> format what is the best formula for obtaining the necessary results?
>
> Many thanks for your consideration,
>
> Marie.
>
>
> --
> Marie1uk
> ------------------------------------------------------------------------
> Marie1uk's Profile:
> http://www.excelforum.com/member.php...o&userid=20984
> View this thread: http://www.excelforum.com/showthread...hreadid=384303
>

[Lewis Clark]

I'm not sure I understand what you are asking for. Do the grades range from
A to G? In the first 2 lines, are you looking for the number of students
who earned at least 5 grades of C or higher? If a pupil is absent, will the
cell for that grade be blank?

"Marie1uk" <[email protected]> wrote in
message news:[email protected]...
>
> Hi there,
>
> I am a teacher and need to analyse my results for review and target
> setting purposes.
>
> I need the spreadsheet to generate results for the following:
>
> Number of pupils scoring 5 grades (or higher) A* - C
> % of pupils scoring 5 grades (or higher) A* - C
> Number of pupils scoring 5 grades (or higher) A* - G
> % of pupils scoring 5 grades (or higher) A* - G
> Number of pupils scoring 1 (or higher) grade A*-G
> % of pupils scoring 1 grade (or higher) grade A* - G
> Number & % of pupils who were absent
>
> In column A will be the pupil's first name, column B will be their
> surname, C their gender and in columns D - O will be where I will input
> their grades (not all cells D - O will necessarily be used). Using this
> format what is the best formula for obtaining the necessary results?
>
> Many thanks for your consideration,
>
> Marie.
>
>
> --
> Marie1uk
> ------------------------------------------------------------------------
> Marie1uk's Profile:
> http://www.excelforum.com/member.php...o&userid=20984
> View this thread: http://www.excelforum.com/showthread...hreadid=384303
>

[Aladin Akyurek]

Maybe:

=COUNTIF(GradeRange,"Abs")/COUNTIF(GradeRange,"*?")

Marie1uk wrote:
> The 2440 was a hypothetical example to illustrate what I wanted, not the
> exact number of grades that the spreadsheet will contain - sorry for the
> confusion. The spreadsheet needs to count all cells that have abs in
> them in a specified range and then divide by those cells that contain
> A*, A, B, C, D, E, F, G, U & Abs to arrive at a % of abs marks out of
> all possible grades.
>
>

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

[Lewis Clark]

Domenic,

I've studied your replies to this thread with great interest. This use of
matrix math is very clever. Thank you for the lesson!

Lewis

[Lewis Clark]

I'm not sure I understand what you are asking for. Do the grades range from
A to G? In the first 2 lines, are you looking for the number of students
who earned at least 5 grades of C or higher? If a pupil is absent, will the
cell for that grade be blank?

"Marie1uk" <[email protected]> wrote in
message news:[email protected]...
>
> Hi there,
>
> I am a teacher and need to analyse my results for review and target
> setting purposes.
>
> I need the spreadsheet to generate results for the following:
>
> Number of pupils scoring 5 grades (or higher) A* - C
> % of pupils scoring 5 grades (or higher) A* - C
> Number of pupils scoring 5 grades (or higher) A* - G
> % of pupils scoring 5 grades (or higher) A* - G
> Number of pupils scoring 1 (or higher) grade A*-G
> % of pupils scoring 1 grade (or higher) grade A* - G
> Number & % of pupils who were absent
>
> In column A will be the pupil's first name, column B will be their
> surname, C their gender and in columns D - O will be where I will input
> their grades (not all cells D - O will necessarily be used). Using this
> format what is the best formula for obtaining the necessary results?
>
> Many thanks for your consideration,
>
> Marie.
>
>
> --
> Marie1uk
> ------------------------------------------------------------------------
> Marie1uk's Profile:
> http://www.excelforum.com/member.php...o&userid=20984
> View this thread: http://www.excelforum.com/showthread...hreadid=384303
>

[Aladin Akyurek]

Maybe:

=COUNTIF(GradeRange,"Abs")/COUNTIF(GradeRange,"*?")

Marie1uk wrote:
> The 2440 was a hypothetical example to illustrate what I wanted, not the
> exact number of grades that the spreadsheet will contain - sorry for the
> confusion. The spreadsheet needs to count all cells that have abs in
> them in a specified range and then divide by those cells that contain
> A*, A, B, C, D, E, F, G, U & Abs to arrive at a % of abs marks out of
> all possible grades.
>
>

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

[Lewis Clark]

Domenic,

I've studied your replies to this thread with great interest. This use of
matrix math is very clever. Thank you for the lesson!

Lewis