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

[kamaruddinjnv]

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: 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. <tbody> </tbody>
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 <tbody> </tbody>
** One example of upgraded marks mentioned here. <tbody> </tbody>
Help: Your a little help save a lot of time of thousands teachers.
for details please see this excel filefor download pl copy and paste this link to address bar.)
http://files.kamaruddin.webnode.com/200000309-0278503710/quesion%20for%20UPGRADING%20FORMULA.xlsx

<tbody>
</tbody>

 

[taurean]

OK.

Suppose if we arrange priorities in sequence in such a way that Priority 1 or P1 to left and P5 to right then arrangement would look like below:
P1 (Numeric Grade) > P2 > P3 > P4 > P5 (XYZ Grade)

Lets take P5 into consideration. It is 2 digit number. If there are no other priorities but only P5 (or XYZ) how do we compare them? It is by their numerical values. The largest would be the one which gets upgrade. Right?
[This is exactly the case when all P1 to P4 are the same for a subject]

Now lets assume that P4 comes into picture. When we have to compare P4 with other subject's P4 then it should be only that priority level and P5 should not have any influence on it. How do we achieve this? We achieve this by multiplying a reasonable number which will make P4 large enough to have no influence of P5 condition. Since XYZ is 2 digit number we multiply P4 by 100.
e.g.
For Hindi and English suppose priorities P1 to P3 are exactly the same. However P4 is different.
P4 for Hindi is 32 and for English is 34 and P5 is 78 and 67 respectively. so when we multiply P4 with 100 and add P5 to it we get:
Hindi : 3200 + 78 = 3278
English : 3400 + 67 = 3467
So we have made P4 powerful enough to be ranked correctly when it is distinguishing criteria.

Do you follow this?

If yes, then rest of levels are plain building blocks. i.e.
For P3 we multiply the number 10000 (notice 2 zeroes added because P4 is also 2 digit number)

So it is simple hierarchy creation where the multiplier increases by 2 zeroes.
P5 : Value * 10^0
P4 : Value * 10^2
P3 : Value * 10^4
P2 : Value * 10^6
P1 : Value * 10^8
And just sum them up to build a single number so that we can quickly test largest n numbers.

Does this help?

 

[kamaruddinjnv]

Hi
Thanks again for explaining so nicely. This helps lot.
Thanking you.
regards
Kamaruddin