Excel: Formula or VBA for upgrading the grade one/two or Nil subject among five subject with various condition.


[TR] [TD="colspan: 11"] I want Fix a formula in D6:D10 for upgrade the grade ( Based on C6:C10) of only one /two subjects or Nil subject acording to no.mentioned in C2 cell. Upgradation will be from lower grade to upper grade. in C=0 then D6:D10 WILL BE SAME AS C6:C10, IF C=1, THEN ONLY ONE SUBJECT WILL BE UPGRADED, which is lowest grade, IF C=2, THEN TWO SUBJECTS GRADE WILL BE UPGRADED FROM LOWEST GRADE. but if in case of tie in Grade among two or three suject then FOLLOWING CONDITION WILL BE APPLIED:
[TABLE="width: 692"]
1- Grade will be upgraded for that subject, in which overall marks will be heighest( F6:F10)
2- If overall marks will tie then, Grade will be upgraded for that subject, in which SA marks will be heighest( G6:G10)
3- If SA marks will tie then, Grade will be upgraded for that subject, in which FA marks will be heighest( H6:H10)
4- BUT IF XYZ MARKS LESS THAN 25% IN ANY SUBJECT, THEN E1 GRADE WILL NOT BE UPGRADED IN CONCERNED SUJECT.
5- GRADE WILL BE UPGRADED from E1 to D, D to C2, C2 to C1, C1 to B2, B2 to B1, B1 to A2. A1 and E2 grade will not be upgraded.
[/TD] [/TR] [TR] [TD]
2 subject upgraded
SUBJECT Obt. GRADE UPGRADED GRADE **Upgraded Table of Obtained Marks for Upscaling Purpose.
Overall Marks SA FA XYZ
HINDI B2 B2 62.00 30.00 32.00 50.00
ENGLISH B1 A2 ** 80.00 50.00 30.00 83.33
MATHS B1 B1 73.00 40.00 33.00 66.67
SCIENCE E1 E1 29.00 14.00 15.00 23.33
SOCIAL SC B1 A2 ** 77.00 55.00 22.00 91.67
[/TD] [/TR] [TR] [TD="class: xl68, width: 376, colspan: 6"]** One example of upgraded marks mentioned here.
[/TD] [/TR] Help: Your a little help save a lot of time of thousands teachers. for details please see this excel file:(for download pl copy and paste this link to address bar.) http://files.kamaruddin.webnode.com/200000309-0278503710/quesion%20for%20UPGRADING%20FORMULA.xlsx [/TABLE]


This question generated 22 answers. To proceed to the answers, click here.

This thread is current as of March 16, 2014.


For more resources for Microsoft Excel