Thanks Guys for the good work. I am writing though with a small problem. I want to sum students grades based on the choices of subjects they made. The situation is like this: Sum three compulsory subjects (Group 1), Choose best two of two or three in group 2, choose best one of two or three in group three, and finally choose any one best subject from the remaining group 2 OR remaining group 3 OR any group 4 subjects. I will greatly apreciate your help.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
Summing columns with repetition
- Thread starter Rutto
- Start date
NARAYANK991
Excel Ninja
Hi ,
Can you please clarify a few points ?
1. How many subjects are available for the students to choose from ? Suppose we assume that the subjects are called A , B , C , D , E , F ,... Let us assume subjects A , B and C are compulsory , from Group 1. How many are the subjects in the remaining 3 groups ?
2. Choose best 2 of 2 or 3 from Group 2 ; choose best 1 of 2 or 3 from Group 3 ; choose best 1 from the remaining subjects in Group 2 or Group 3 or Group 4. Can you explain this with an example ?
3. If you already have some data , can you copy + paste some data for just 1 student ?
Narayan
Can you please clarify a few points ?
1. How many subjects are available for the students to choose from ? Suppose we assume that the subjects are called A , B , C , D , E , F ,... Let us assume subjects A , B and C are compulsory , from Group 1. How many are the subjects in the remaining 3 groups ?
2. Choose best 2 of 2 or 3 from Group 2 ; choose best 1 of 2 or 3 from Group 3 ; choose best 1 from the remaining subjects in Group 2 or Group 3 or Group 4. Can you explain this with an example ?
3. If you already have some data , can you copy + paste some data for just 1 student ?
Narayan
Fine Narayan,
Thanks for responding.
ENG 33 KIS 46 MAT 54 BIO 55 PHY 67 CHEM43 HG78 GEO CRE HSC 34 AGR BST TOTAL
(The numbers after the subjects are students marks)
ENG, KIS, & MAT are compulsory,
Group 2 comprises BIO, PHY & CHEM. A student can opt to do any two or all the three.
Group 3 is made up of HG, GEO, & CRE. A student needs to choose at least one from here and that means they can have 1, 2 or 3 subjects from here.
Group 4 has HSC, AGR & BST. A student can choose one or two or NONE from here.
ONLY 7 subjects are graded but a student has a choice to do upto NINE subjects.
GRADING is carried out thus: SUM(All group 1+ TWO best from group 2 + ONE best from group 3+ ONE OTHER best from the remaining in group 2 OR remaining in group 3 or group 4.
This is lenghty but I hope it clarifies the point.
Many Thanks.
Thanks for responding.
ENG 33 KIS 46 MAT 54 BIO 55 PHY 67 CHEM43 HG78 GEO CRE HSC 34 AGR BST TOTAL
(The numbers after the subjects are students marks)
ENG, KIS, & MAT are compulsory,
Group 2 comprises BIO, PHY & CHEM. A student can opt to do any two or all the three.
Group 3 is made up of HG, GEO, & CRE. A student needs to choose at least one from here and that means they can have 1, 2 or 3 subjects from here.
Group 4 has HSC, AGR & BST. A student can choose one or two or NONE from here.
ONLY 7 subjects are graded but a student has a choice to do upto NINE subjects.
GRADING is carried out thus: SUM(All group 1+ TWO best from group 2 + ONE best from group 3+ ONE OTHER best from the remaining in group 2 OR remaining in group 3 or group 4.
This is lenghty but I hope it clarifies the point.
Many Thanks.
NARAYANK991
Excel Ninja
Hi ,
Thanks for the detailed clarification. Lots of food for thought !
I'll need some time to absorb all of this and see if a solution using only formulae occurs to me. Are you averse to using VBA , since with some programming , a solution will be easier to devise.
Narayan
Thanks for the detailed clarification. Lots of food for thought !
I'll need some time to absorb all of this and see if a solution using only formulae occurs to me. Are you averse to using VBA , since with some programming , a solution will be easier to devise.
Narayan
NARAYANK991
Excel Ninja
Hi ,
Let us assume that the data starts from cell A6 , and the column A has the student IDs , column B has the ENG marks , column C the KIS marks and so on. The subjects will cover columns B through column M.
Use column N for the Group 1 Marks , column O for the Group 2 marks ,... and the column R for the total marks.
Enter the following formulae in cells N6 through R6 :
N6 : =SUM(B6:D6)
O6 : =LARGE(E6:G6,1)+LARGE(E6:G6,2) 'this will take the top 2 marks from Group 2
P6 : =LARGE(H6:J6,1) 'the best mark from Group 3 subjects
Q6 : =MAX(K6,L6,M6,SUM(E6:G6)-O6,IFERROR(LARGE(H6:J6,2),0)) 'top mark from the remaining subjects from Groups 2 , 3 and 4
R6 : =SUM(N6:Q6)
Narayan
Let us assume that the data starts from cell A6 , and the column A has the student IDs , column B has the ENG marks , column C the KIS marks and so on. The subjects will cover columns B through column M.
Use column N for the Group 1 Marks , column O for the Group 2 marks ,... and the column R for the total marks.
Enter the following formulae in cells N6 through R6 :
N6 : =SUM(B6:D6)
O6 : =LARGE(E6:G6,1)+LARGE(E6:G6,2) 'this will take the top 2 marks from Group 2
P6 : =LARGE(H6:J6,1) 'the best mark from Group 3 subjects
Q6 : =MAX(K6,L6,M6,SUM(E6:G6)-O6,IFERROR(LARGE(H6:J6,2),0)) 'top mark from the remaining subjects from Groups 2 , 3 and 4
R6 : =SUM(N6:Q6)
Narayan
Hi,
Igot it!
Your formula is perfect.
The most important thing I've learnt is IFERROR.
Was able to modify part two of step Q6 so that it looks like: =IFERROR(E6:G6,3),0) to skip subtracting O6.
Was also able to put the formula together in a single cell. Mine finally looks thus:
=SUM(B6:D6)+SUM(LARGE(E6:G6,{1,2}))+LARGE(H6:J6,1)+MAX(IFERROR(LARGE(E6:G6,3),0),IFERROR(LARGE(H6:J6,2),0),MAX(K6:M6))
Thanks so much Narayan. Didn't expect to get such a timely reply but here it is. Long live CHANDOO.ORG
Rutto.
Igot it!
Your formula is perfect.
The most important thing I've learnt is IFERROR.
Was able to modify part two of step Q6 so that it looks like: =IFERROR(E6:G6,3),0) to skip subtracting O6.
Was also able to put the formula together in a single cell. Mine finally looks thus:
=SUM(B6:D6)+SUM(LARGE(E6:G6,{1,2}))+LARGE(H6:J6,1)+MAX(IFERROR(LARGE(E6:G6,3),0),IFERROR(LARGE(H6:J6,2),0),MAX(K6:M6))
Thanks so much Narayan. Didn't expect to get such a timely reply but here it is. Long live CHANDOO.ORG
Rutto.
NARAYANK991
Excel Ninja
Hi ,
Thanks for the feedback. Keep visiting.
Narayan
Thanks for the feedback. Keep visiting.
Narayan
Narayank, nice one.
I was thinking if we could use this formula instead (trying to avoid iferror).
=MAX(MIN(E7:G7),LARGE(H7:J7,2),K7,L7,M7)
From columns B to D - all subjects are chosen so there are no others to choose from
From E to G- best 2 have already been chosen so you are left with the 3rd (1.e the minimum(E:G)
From H to J, the best has been chosen and so the only available choice is the 2nd largest, hence large(h:j,2) -
And then you have the others in K,L and M
Just my thought anyways
I was thinking if we could use this formula instead (trying to avoid iferror).
=MAX(MIN(E7:G7),LARGE(H7:J7,2),K7,L7,M7)
From columns B to D - all subjects are chosen so there are no others to choose from
From E to G- best 2 have already been chosen so you are left with the 3rd (1.e the minimum(E:G)
From H to J, the best has been chosen and so the only available choice is the 2nd largest, hence large(h:j,2) -
And then you have the others in K,L and M
Just my thought anyways
NARAYANK991
Excel Ninja
Hi ,
Thanks for your thoughts. I will try to reply in detail tomorrow ( it's bedtime here ! ).
However , I was just checking the MIN function , and I see that if a cell is empty , the MIN function does not take it as = 0. Hence , for Group 2 , i.e. columns E , F and G , if the student has opted for all 3 subjects , then the MIN function will return the lowest value from the marks for the 3 subjects. But if the student opted for only 2 out of the 3 subjects , and the marks cell for the third subject was empty , then the MIN function would return the lower value from the 2 subjects which had marks against them ! This is not what we want.
Keep those ideas coming.
Narayan
Thanks for your thoughts. I will try to reply in detail tomorrow ( it's bedtime here ! ).
However , I was just checking the MIN function , and I see that if a cell is empty , the MIN function does not take it as = 0. Hence , for Group 2 , i.e. columns E , F and G , if the student has opted for all 3 subjects , then the MIN function will return the lowest value from the marks for the 3 subjects. But if the student opted for only 2 out of the 3 subjects , and the marks cell for the third subject was empty , then the MIN function would return the lower value from the 2 subjects which had marks against them ! This is not what we want.
Keep those ideas coming.
Narayan
Hi Guys.
Like you rightly put it, IFERROR would have to feature somewhere to avoid violating the conditions for summing up the various subjects.
I also realised that students could still be graded even with a compulsory subject missing (e.g any B:D).
I had to use this formula to ensure an error was returned if any compulsory subject was missing. =IFERROR(LARGE(B6:D6,{1,2,3})e.t.c.
Sorry for my belated comment.
Like you rightly put it, IFERROR would have to feature somewhere to avoid violating the conditions for summing up the various subjects.
I also realised that students could still be graded even with a compulsory subject missing (e.g any B:D).
I had to use this formula to ensure an error was returned if any compulsory subject was missing. =IFERROR(LARGE(B6:D6,{1,2,3})e.t.c.
Sorry for my belated comment.