Roll up, roll up!

I have a spreadsheet which I’m using to try to analyse exam results for school pupils. Most pupils will study 8 subjects, but some will only do 6 or 7. Each pupil will be awarded a grade between 1 and 6 for each subject they sit.

I have a formula which will count the number of grade 5s and 6s, a separate formula which counts 3s and 4s and a third which counts 1s and 2s. Each of these formulae is predicated on there being exactly 8 subjects per pupil, but I need it to automatically adapt to a pupil having only 6 or 7 grades (or 5 or 9 for that matter).

Make sense?

Each pupil is identified by an ID number and the data is sorted by this number so that all the grades for a single pupil are grouped. The formula which counts the 5s and 6s is:

=IF(AND($C2=$C3,ISNUMBER($D2) ),IF($C2<>$C1,(COUNTIF($D2:$D9,5)+(COUNTIF($D2:$D9,6))),””),””)

The context is shown in the screenshot below. The problem is the fixed cell range (D2:D9). How can I vary this to count only the 5s and 6s for the same pupil?

scqf by stuartie_c, on Flickr

run a column down summing each of the required counts, define the last record with an equation, then filter on that record? You’ll get the number of records then.

Job for SAS or R really.

EDIT for explicit equations, don’t sum the range with COUNTIF, just use an IF statement

Example: Suppose cumulative sum of 3’s is in column I then…

for row 2: Column I:

IF($C2=$C1, IF(D2=’3′,I1+1,I1),IF(D2=’3′,1,0))
Repeat for other classes

Then for Last column, say column Z
IF($C2=$C3,0,1)

you can filter on this to get the cumulative sums.

Thanks djaustin.

I’ll try this in the morning – been at it all day so a bit frazzled now.

I want to try to avoid any filtering of data because it is designed so that a (non-expert) user can simply paste in 2 columns of data exported from a database and the analysis is done automatically. This works provided there are exactly 8 subject grades for every pupil (the important numbers appear in the coloured cells just out of shot.)

SC

Where your formula says $D2:$D9 this is essentially $Dn:$D(n+7) and you need the 7 to be able to vary.

I think you should be able to do this using the Countif to count the number of the cells in column D from the current position to the bottom that are equal the current student ID. Then (possibly using the indirect function to allow you to refer to the range) you should be able to refer to the cell D of the current row plus the result from this count.

I should be able to give a more detailed help tomorrow if you’re still stuck.

To do this ^ you could use constructs such as:

=COUNTIF(OFFSET(D2,0,0,E2,1),5)

The OFFSET defines a range starting at D2 (offset by 0 rows and 0 columns) of height E2 and width 1.

This assumes that your value in E2 is always available (and correct) to show the number of grades to set the size of the range – it should be possible to calculate this value with a couple of extra columns if necessary.

Fantastic!

Thanks for all the suggestions guys -m-‘s formula does exactly what I want (with a couple of absolute refs thrown in to allow filling right).

That formula in full…

=IF(AND($C2=$C3,ISNUMBER($D2) ),IF($C2<>$C1,(COUNTIF(OFFSET($D2,0,0,$E2,1),5)+(COUNTIF(OFFSET($D2,0,0,$E2,1),6))),””),””)

I’ll need to do a bit more testing to see if the offset value stored in E2 needs to have 1 subtracted, but at the moment it appears to work.

Good job!