How to calculate my MBA results?

[Skeptical]

Hi everybody at MrExcel,

We are from a university. We have SIX specializations,

Specializations------Codes

Finance ----- F,
Marketing ----M,
HR ----H,
Finance with Marketing ----FM
Finance with HR----FH
HR with Marketing------HM

We have six subjects in each combination i.e.

Combination---Subjects

F--------F1,F2,F3,F4,F5,F6
H------------H1,H2,H3,H4,H5,H6
M------M1,M2,M3,M4,M5,M6
FM----------F1,F2,F3,M1,M2,M3
HM----------H1,H2,H3,M1,M2,M3
FH------------F1,F2,F3,H1,H2,H3

Total marks 150*6 subjects=900

Result Conditions:
In each subject, student should score>=75 marks in Total for a Pass
The marks in External should be >= 40 marks
The marks in Internal should be >= 25 marks
Awarding of Class: 50% - 60% is Second Class 60% - 70% is First class >= 70% is FCD (First Class with Distinction) <tbody> </tbody>

<tbody>
</tbody>

I have Results column and Class column, What formula should be used there to get my results calculated?
I have tried with so many IFs but that is so inefficient, I request humbly everybody please help me on this.

I have attached a sample workbook here .

Thank you very much
Skeptical

 

[Peter_SSs]

I still don't understand.

- For that first section, up to column T, could you give us the results you expect for say the first 5 students and explain how you arrived at those results?

- Could you tell us where those results should go?

 

[Skeptical]

Peter, thank you so much for your patience for follow-ups and patience reading my posts.

First I will explain about the subject combinations in the Excel sheet.
A column: Student,
B Column: Branch he opted for,
C col - T cols: All Finance subjects,
U col - AL cols: Marketing subjects,
AM col - BD cols: HR subjects,
BE col: Results whether Pass or Fail,
BF col: Class or Grade.

But using HTML maker I have given the screenshot only for some of the subjects due to the space constraints. I could show only Finance and some of the Marketing subjects. Same way HR subjects are also there(from column AM to col BD), but I couldn't show.

All subject marks are presented as Internal, External and Total marks. The results should be displayed in the BE column. The conditions for the Pass or Fail are follows.

The Result conditions: In each subject

Student should score >=75 marks total for a pass AND
The marks in External should be >= 40 marks AND
The marks in Internal should be >= 25 marks

As I mentioned earlier, A Marketing specialization guy will study all of the Marketing subjects, Finance guy should study all Finance subjects like wise for HR. A dual specialization guy i.e. MF(Marketing with Finance) will study First 3 Finance subjects and First 3 Marketing subjects. i.e. F1,F2,F3,M1,M2,M3. MH (Marketing with HR) will study First 3 Marketing subjects and First 3 HR subjects. i.e. M1,M2,M3,H1,H2,H3. Like wise for FH and other combinations. This was mentioned clearly while I post it for the first time.

Awarding of Class or Grade:
Total marks: 6*150 = 900
50% - 60% is Second Class
60% - 70% is First class
>= 70% is FCD (First Class with Distinction)

For Pass or Fail criteria, some examples of marks and results:

a) External 40 + Internal 25 = 65 Marks (Fail)
b) External 39 + Internal 35 = 74 Marks (Fail)
c) External 51 + Internal 24 = 75 Marks (Fail)
d) External 40 + Internal 35 = 75 Marks (Pass)
e) External 50 + Internal 25 = 75 Marks (Pass)

I think I have explained the case clearly. Please ask me if you still need anything
Skeptical

 

[Peter_SSs]

Suggestion:

Make up a dummy set of data with ..

- only 2 specializations: Finance (F) and Marketing (M)
- for each of those only 3 subjects: F1, F2, F3 and M1, M2, M3 (each with Int, Ext, Total)

Show sample data (should be only about 20 columns), expected results & give explanation in relation to that. If we can comprehend that requirement, it will probably not be too much to expand to your larger actual data.

 

[Skeptical]

Peter,

I have two datasets, one is minimized data set as per your latest requirement and the actual sample data. You can find some more explanation there.
Both can be obtained by the below Google drive link. Let me know if you need something else. Thank you.

https://drive.google.com/file/d/0B-uTVzgsjvZRLVVpcFgzekFaMGs/view?usp=sharing
Skeptical

 

[Peter_SSs]

Any chance you could post the small data & its explanation here? That was one of the reasons for suggesting a smaller sample.

 

[Skeptical]

Student	Branch	F1 Int	F1 Ext	F1 Tot	F2 Int	F2 Ext	F2 Tot	M1 Int	M1 Ext	M1 Tot	M2 Int	M2 Ext	M2 Tot	Results	Grade	Reason
Student-1	M							30	28	58	33	22	55	Fail		Condition-1 not fulfilled
Student-2	F	35	36	71	45	22	67							Fail		Condition-1&2 not fulfilled
Student-3	FM	35	42	77				40	44	84				Pass		All Conditions fulfilled in all subjects
Student-4	M							30	45	75	26	44	70	Fail		All conditions fulfilled only in some subjects i.e fulfilled in M1
Student-5	F	43	47	90	45	45	90							Fail		All conditions fulfilled only in some subjects i.e fulfilled in F2
Student-6	FM	26	41	67				26	47	73				Fail		Condition-3 not fulfilled in the 2 subjects
Student-7	F	26	41	67	34	54	88							Fail		Condition-3 not fulfilled in the F1 subject
Student-8	F	39	41	80	32	56	88							Pass		All Conditions fulfilled in all subjects
Student-9	F	49	40	89	24	39	63							Fail		No condition fulfilled in F2
Student-10	FM	43	37	80				43	33	76				Fail		Condition-2 not fulfilled in both subjects
Student-11	M							12	34	46	26	42	66	Fail		Condition-1 not fulfilled in both subjects

<tbody>
</tbody>

Conditions:
1. In each subject, student should score>=75 marks total for a pass AND
2. The marks in External should be >= 40 marks AND
3. The marks in Internal should be >= 25 marks.

Note:
1. Maximum Internal Marks: 50
2. Maximum External Marks: 100
3. Total Exam marks: 150

Those guys who takes M (Marketing) will study only M1 and M2,
Those guys who takes F (Finance) will study only F1 and F2,
Those guys who takes FM (Finance with Marketing) will study only F1 and M1 only.

Awarding of Class:
1. Total marks: is 4*150 = 600
2. 50% - 60% is Second Class
3. 60% - 70% is First class
4. >= 70% is FCD (First Class with Distinction)

Please ask us for any other information. Thanks for your patience, Peter.

Skeptical

 

[Peter_SSs]

Thanks.

I'm struggling with a few of your examples but I'm hoping it is that you have made some errors.

- I don't see why you have Student-5 as a Fail. It appears to me they have met all conditions in both subjects.
- I agree with your Pass/Fail for each of Students 6, 7 and 11, but disagree with which conditions fail. It seems to me that you may have mixed up condition 1 with condition 3.

Anyway, see how this monster goes for obtaining the Pass/Fail result.
Formula id just copied down.

If I have it right, then I think all you should need to do to change to your 6 subjects per specialization, is to change cell R1 (or whatever cell you move that to in the end) to 6.

Once we get the Pass/Fail sorted, you will need to clarify further about the Grade with examples & explanation of the examples.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Student	Branch	F1	F1	F1	F2	F2	F2	M1	M1	M1	M2	M2	M2	Results		Subjects per Specialization:	2
2			Int	Ext	Tot	Int	Ext	Tot	Int	Ext	Tot	Int	Ext	Tot
3	Student-1	M							30	28	58	33	22	55	Fail
4	Student-2	F	35	36	71	45	22	67							Fail
5	Student-3	FM	35	42	77				40	44	84				Pass
6	Student-4	M							30	45	75	26	44	70	Fail
7	Student-5	F	43	47	90	45	45	90							Pass
8	Student-6	FM	26	41	67				26	47	73				Fail
9	Student-7	F	26	41	67	34	54	88							Fail
10	Student-8	F	39	41	80	32	56	88							Pass
11	Student-9	F	49	40	89	24	39	63							Fail
12	Student-10	FM	43	37	80				43	33	76				Fail
13	Student-11	M							12	34	46	26	42	66	Fail
Sheet1

 

[Skeptical]

Peter,

Thank you so much for your efforts, firstly.

Even if I confused with all of my explanations, you have understood the correct thing (you have a great common sense here). Yes, I made mistake in typing the conditions. I mixed up the Condition-1 with Condition-3. Somewhere, results are correct but reasons for the Fail are incorrectly typed. Anyways your formula showing "Fail" for all of the cases. I tried changing the values but it never changed the results. I think it all needs is the fine tuning of the formula. Later we will see the Grades.

Thank you
Skeptical.

 

[Peter_SSs]

Anyways your formula showing "Fail" for all of the cases.

Well, for the sample I posted you can see that is not the case. Things to check ..
- Do you have any text entries in columns C:N below those 2 heading rows?
- Did you remember to include the value in cell R1 on your sheet?
- Are your headings/labels in column B and rows 1& 2 identical to the sample you posted in post #16 & mine in post #17? Check spelling and any leading or trailing spaces.

 

[Skeptical]

Peter,

This is working fine. I made it to work for 6 subjects and taken care of that R1 part. I tried with different marks combinations. Its working like a Gun. Thank you so much, Peter. At last you have achieved it. You're the man.
Skeptical