Few questions

[Neil Greenough]

It is exam results day in the UK today and so I am trying to analyse our
results and need some help.

Columns A & B = person info
Column C = Another number (see further down)
Column D = Tutor
Column E = Class
Column F = Target Grade
Column G = Mock Exam Grade
Column H = Actual grade.

Now within certain ranges within these columns, for example rows 1:30, I
want to do the following:-

- "Work out the percentage of grade A*-C grades for that group" - each entry
in column H has a grade ranging from A*, A, B,C,D,E,F etc.... I want to work
out how many of the letters in column H are actually A*, A, B and C.

- "Work out the target percentage of A*-C grades" - I want to work out in a
selected number of rows, for example rows 1:30, how many of the letters in
column F are A*,A,B,C.

- In column C, pupils all have different numbers ranging from 1-200. I want
to look at each row and if the number in column C is above 95, They should
have the letter A*, A, B or C in column H. I want to see how many do have
this and how many don't.

Any help would be appreciated

 

[Frank Kabel]

Hi
see inline

[...]
- "Work out the percentage of grade A*-C grades for that group" -

each entry in column H has a grade ranging from A*, A, B,C,D,E,F
etc.... I want to work out how many of the letters in column H are
actually A*, A, B and C.

for counting
=SUM(COUNTIF(H1:H30,{"A~*","B","C"}))

for percentage:
=SUM(COUNTIF(H1:H30,{"A~*","B","C"}))/COUNTA(H1:H30)

- "Work out the target percentage of A*-C grades" - I want to work
out in a selected number of rows, for example rows 1:30, how many of
the letters in column F are A*,A,B,C.

for counting
=SUM(COUNTIF(F1:F30,{"A~*","B","C"}))

for percentage:
=SUM(COUNTIF(F:F30,{"A~*","B","C"}))/COUNTA(F1:F30)

- In column C, pupils all have different numbers ranging from 1-200.
I want to look at each row and if the number in column C is above 95,
They should have the letter A*, A, B or C in column H. I want to see
how many do have this and how many don't.

for matching values:
=SUMPRODUCT((C1:C100>95)*(H1:H100={"A*","A","B","C"}))

for non matching values:
=SUMPRODUCT((C1:C100>95)*(H1:H100={"D","E","F"}))

 

[Neil Greenough]

Thanks Frank - worked a treat

Hi
see inline

[...]
- "Work out the percentage of grade A*-C grades for that group" -

each entry in column H has a grade ranging from A*, A, B,C,D,E,F
etc.... I want to work out how many of the letters in column H are
actually A*, A, B and C.

for counting
=SUM(COUNTIF(H1:H30,{"A~*","B","C"}))

for percentage:
=SUM(COUNTIF(H1:H30,{"A~*","B","C"}))/COUNTA(H1:H30)

- "Work out the target percentage of A*-C grades" - I want to work
out in a selected number of rows, for example rows 1:30, how many of
the letters in column F are A*,A,B,C.

for counting
=SUM(COUNTIF(F1:F30,{"A~*","B","C"}))

for percentage:
=SUM(COUNTIF(F:F30,{"A~*","B","C"}))/COUNTA(F1:F30)

- In column C, pupils all have different numbers ranging from 1-200.
I want to look at each row and if the number in column C is above 95,
They should have the letter A*, A, B or C in column H. I want to see
how many do have this and how many don't.

for matching values:
=SUMPRODUCT((C1:C100>95)*(H1:H100={"A*","A","B","C"}))

for non matching values:
=SUMPRODUCT((C1:C100>95)*(H1:H100={"D","E","F"}))