Sumif with a division compared to a reference

[tpayne89]

Hi,

Hoping for some help on a formula that we need to help grade our Maths students this year, only on topics that we have taught that are in the final tests, but allowing extra credit if they do score well on some of the topics not taught.

I have attached a mock-up spreadsheet of the scenario. I have included 5 students, in the green on the right are their scores for each question. Above each question, there is a label of C or E standing for Core and Enhanced respectively. As well as the marks for that question. The 'Core' questions are those that we have taught the subject matter for, the 'Enhanced' are those we have not.

I have got a formula working fine for calculating the students Core marks and Percentage, but it is the enhanced I am stuck on.

The concept of the enhanced marks is that for each enhanced question that a student answers, it will only be counted in the total enhanced marks and percentage if it will improve their core percentage.

So say for example a student has scored 8/16 on the core questions so has a core % of 50%. If they then scored 1/4 on an enhanced question, we will ignore this as it will lower their overall percentage. However, if they scored 3/4 of 4/4 on an enhanced question, then we would include it. So lets say the student scored 3/4, their enhanced mark would become 11/20 and enhanced percentage therefore 55%.

I have entered in how the results should look manually, I have left blank the space that I have been using to try and make a formula to achieve this..

I have been wondering from a bit of googling if maybe it should look something like this in cell F4

=SUM(IF(ISNUMBER(1/H1:L1),IF(H2:L2="E",IF(H5:L5/H2:L2>C5,H5:L5))))

I was hoping that this would first check if the question was an 'E' type question, then if that is the case, divide the mark scored by the mark available for the question and compare this result to the core percentage. If it was greater, include it in the sum.

Once the formula for the mark is sorted, I would think the formula for the percentage should follow fairly easily.

Any help would be very much appreciated.

Thanks
Tom

[hrlngrv]

Isn't it always best to determine what students have learned rather than what teachers have taught?

Note: since the C4 formula includes *100, you have to divide it by 100 to use it in criteria with respect to scores from enhancement columns K and L.

The enhanced calculations are easier using SUMPRODUCT.

F4:

Please Login or Register  to view this content.

G4:

Please Login or Register  to view this content.

Select F4:G4 and fill down into F5:G8.

[etaf]

i used an IF
but dont you want to compare , the C with the C+E to see % is greater
so in first example
total marks possible from H1:L1 is 20 5x4, s possible marks are 4 ,4,4,4,4
and then the marks below for C and E , so H3:L3 4,3,4,1,4 C,C,C,E,E

so we have 4,3,4 out of 4,4,4. = 11/16 as a percentage
and then the total
4,3,4,1,4 = 16 , out of 4,4,4,4,4 so 16/20 as a percentage
and if the 2nd % is greater than the first use that

BUT you show 15 in D4 & NOT 16
AND 13 in D8 not 14

wondered why ?
otherwise i used an IF
=IF((SUM(H4:L4)/SUM(H$1:L$1))*100>C4,SUM(H4:L4),B4)
=IF((SUM(H4:L4)/SUM(H$1:L$1))*100>C4,(SUM(H4:L4)/SUM(H$1:L$1))*100,C4)

BUT I maybe misunderstanding the whole concept , as you have a solution now using sumproduct , but as i have spent time working out , i thought i would put it out there, but as i say maybe missing the point

[Richard Buttrey]

In G4

Please Login or Register  to view this content.

In F4

Please Login or Register  to view this content.

Since the "C" & "E" columns are each together then there's no point in using SUMIFS. Just address the columns directly

[tpayne89]

Wow thanks for the quick replies everyone, I have just tried the formula that hrlngrv first suggested, which seems to do the job - (I'm very pleased :-) and it was actually fairly close to a sumproduct that I had been trying)

I've also tested yours etaf and Richard which certainly achieve something I wasn't able to do myself, but is a slight misinterpretation (as you guessed etaf) of what I need to do. (Of course it may always help someone else like me who googles all these forums to try and find solutions before posting) Let me try and explain...

So take the first student, on the core questions, they have scored 11/12 giving 92%.
We then need to look at the enhanced questions, but we need to consider each one individually, as that 92% must now be the lowest possible score, it will only ever be increased.
So for Q4, the student has scored 1/4 which is 25%. If we were to include this then their overall % would be lowered, so we do not.
However in Q5, the student has scored 4/4, 100% and therefore we will include this question as it will improve their score.

Certainly makes it a tricky little calculation :-)

Also in response to your first line hrlngrv 'Isn't it always best to determine what students have learned rather than what teachers have taught?'. I think in essence we are probably much the same in this line of thinking. However, with the pandemic, the DFE have instructed that we should only 'examine' students and grade them on topics that we have had time to teach. Frustratingly though, this advice really is only aimed at schools, I teach in a college. This is not the same context, the students that I am teaching have failed GCSE Maths one or more times already, which also means in theory they have previously been taught the entire course. So as a compromise, we came up with this system. They get a minimum % based on the topics we have taught, but do have the ability to gain extra credit if you like on topics that we haven't taught but they remember from prior schooling. If we draw grade boundaries on the core scores then I think we achieve a good level of fairness.

Thanks,
Tom