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

[kamaruddinjnv]

[TABLE="class: outer_border, width: 500, align: left"]
<tbody>[TR]
[TD][TABLE="width: 692"]
<tbody>[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:[/TD]
[/TR]
[TR]
[TD]1-[/TD]
[TD="colspan: 9"]Grade will be upgraded for that subject, in which overall marks will be heighest( F6:F10)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2-[/TD]
[TD="colspan: 10"]If overall marks will tie then, Grade will be upgraded for that subject, in which SA marks will be heighest( G6:G10)[/TD]
[/TR]
[TR]
[TD]3-[/TD]
[TD="colspan: 10"]If SA marks will tie then, Grade will be upgraded for that subject, in which FA marks will be heighest( H6:H10)[/TD]
[/TR]
[TR]
[TD]4-[/TD]
[TD="colspan: 10"]BUT IF XYZ MARKS LESS THAN 25% IN ANY SUBJECT, THEN E1 GRADE WILL NOT BE UPGRADED IN CONCERNED SUJECT.[/TD]
[/TR]
[TR]
[TD]5-[/TD]
[TD="colspan: 10"]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]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 598"]
<tbody>[TR]
[TD][/TD]
[TD]2[/TD]
[TD="colspan: 3"]subject upgraded[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SUBJECT[/TD]
[TD]Obt.
GRADE[/TD]
[TD]UPGRADED GRADE[/TD]
[TD]**Upgraded[/TD]
[TD="colspan: 5"]Table of Obtained Marks for Upscaling Purpose.[/TD]
[/TR]
[TR]
[TD]Overall Marks[/TD]
[TD]SA[/TD]
[TD]FA[/TD]
[TD][/TD]
[TD]XYZ[/TD]
[/TR]
[TR]
[/TR]
[TR]
[TD]HINDI[/TD]
[TD]B2[/TD]
[TD]B2[/TD]
[TD][/TD]
[TD]62.00[/TD]
[TD]30.00[/TD]
[TD]32.00[/TD]
[TD]50.00[/TD]
[/TR]
[TR]
[TD]ENGLISH[/TD]
[TD]B1[/TD]
[TD]A2[/TD]
[TD]**[/TD]
[TD]80.00[/TD]
[TD]50.00[/TD]
[TD]30.00[/TD]
[TD]83.33[/TD]
[/TR]
[TR]
[TD]MATHS[/TD]
[TD]B1[/TD]
[TD]B1[/TD]
[TD][/TD]
[TD]73.00[/TD]
[TD]40.00[/TD]
[TD]33.00[/TD]
[TD]66.67[/TD]
[/TR]
[TR]
[TD]SCIENCE[/TD]
[TD]E1[/TD]
[TD]E1[/TD]
[TD][/TD]
[TD]29.00[/TD]
[TD]14.00[/TD]
[TD]15.00[/TD]
[TD]23.33[/TD]
[/TR]
[TR]
[TD]SOCIAL SC[/TD]
[TD]B1[/TD]
[TD]A2[/TD]
[TD]**[/TD]
[TD]77.00[/TD]
[TD]55.00[/TD]
[TD]22.00[/TD]
[TD]91.67[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 376"]
<tbody>[TR]
[TD="class: xl68, width: 376, colspan: 6"]** One example of upgraded marks mentioned here.[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Help: Your a little help save a lot of time of thousands teachers.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]for details please see this excel filefor download pl copy and paste this link to address bar.)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]http://files.kamaruddin.webnode.com/200000309-0278503710/quesion%20for%20UPGRADING%20FORMULA.xlsx[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[kamaruddinjnv]

I am uploading file for more information.

http://files.kamaruddin.webnode.com/200000313-eb88bec838/upgrading new problem.xlsx

Thanks.

 

[kamaruddinjnv]

Hi Mr Shrivallabha
Please see this file to solve the new problem mentioned in previous post.

http://files.kamaruddin.webnode.com/200000313-eb88bec838/upgrading new problem.xlsx

 

[taurean]

Indeed, that remains a possibility. Please paste following formula in cell M6:

=(N6*100000000+F6*1000000+G6*10000+H6*100+J6)*(J6>25)-COUNTIFS($F$6:F6,F6,$G$6:G6,G6,$H$6:H6,H6,$J$6:J6,J6)

And then copy down the formula. Then it will upgrade the first n tied subjects where n = 1 or 2.

 

[kamaruddinjnv]

Hi Mr Shriballabha,

You are great. now its works perfectly with this formula. Again I am thankful to you that you have take a lot of interest and pain to solve my problem. with your help I can save so many hours manual work of mine and also a large number of teachers.
very very thanks.
Good night.

regards.
Kamaruddin

 

[kamaruddinjnv]

Hi Mr Shrivallabha,
[TABLE="width: 721"]
<colgroup><col><col><col><col><col><col><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 11"] Again I am giving you a trouble, hope you don't mind it. (1) I am facing againg one problem regarding upgrading formula. This formula was working best but in one example it is giving error. If all grade ( without upgrade) are A1 then this formula giving error.(#N/A). Please rectify it so I may print my result and also some other school result.[/TD]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[TD="colspan: 11"](2) with help of your formula I prepared a sheet for upgrading the whole class grade in one sheet,that is second sheet in this workbook. please see my work and rectify in this sheet also for that problem. Thanks in advance. please see roll no 19 for this error

with regards

Kamaruddin

Please see uploaded file
http://files.kamaruddin.webnode.com/200000351-90a51919fc/upgraded formula error.xlsx[/TD]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]

 

[kamaruddinjnv]

[TABLE="width: 721"]
<colgroup><col><col><col><col><col><col><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 11"]Hi Mr ShriVallabha, Again I am giving you a trouble, hope you don't mind it. (1) I am facing again one problem regarding upgrading formula. This formula was working best but in one example it is giving error. If all grade ( without upgrade) are A1 then this formula giving error.(#N/A). Please rectify it so I may print my result and also some other school result.[/TD]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[TD="colspan: 11"](2) with help of your formula I prepared a sheet for upgrading the grade of whole class that is second sheet in this workbook. please see my work and rectify in this sheet also for that problem. Thanks in advance.[/TD]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]
please see uploaded file for more information:

http://files.kamaruddin.webnode.com/200000351-90a51919fc/upgraded formula error.xlsx

with regards
Kamaruddin

 

[taurean]

I am little confused with first sheet. The results in cell E6:E10 seem wrong to me.

If you modify the formula in D6 as below:
=IF(OR($C$2=0,$C6="A1"),C6,IF(LARGE($M$6:$M$10,$C$2)<=M6,VLOOKUP(C6,$O$3:$P$10,2,0),C6))
and copy down then does it work for you?

If that works then can you try to implement in Sheet2 as well? I see that you have done it quite nicely. Well done. Feel free to come back if needed.

 

[kamaruddinjnv]

Hi,
Thanks for reply. Instead of this following formula I have use a formula
=IF($C6="A1",C6,IF(C2=0,C6,IF(LARGE($M$6:$M$10,$C$2)<=M6,VLOOKUP(C6,$O$3:$P$10,2,0),C6)))
This is also similar to your formula. I found this solution just after sending the threads. and it works.

=IF(OR($C$2=0,$C6="A1"),C6,IF(LARGE($M$6:$M$10,$C$2)<=M6,VLOOKUP(C6,$O$3:$P$10,2,0),C6))

one more thing I could not understand till now your combo number system. In which logic it will be develope I eager to know. where I can get it details theory.


Thanks again for giving me attention.
with regards
Kamaruddin

 

[taurean]

Well done Kamaruddin .

I will try to explain.

First Part:
We created a simple lookup table for each grade (except for the lowermost and topmost grade) so that when it is applicable to for an upgrade we simply search the higher grade written against it. This portion is handled by the simple VLOOKUP formula.
VLOOKUP(C6,$O$3:$P$10,2,0),C6)

Second Part:
We needed to figure out in which cases we need to apply formula written in the first part. This is where the combonumber (it is just a phrase I assigned. There is no such theory) came into the picture. In your case the constraint was 0 to 2 number of subjects to be upgraded. However, the formula can work for any number of upgrades up to 5 since we have 5 subjects.

The combonumber basically builds up the ranking out of the criteria in their specified order i.e. the topmost criterion has the highest value. Or we can call them priority.

Here the first criterion was grade (which was non numeric) so it needed to be converted to numeric result which we could use in the combonumber. So again we used a function MATCH to create numeric grade.
=IFERROR(MATCH(C6,$O$4:$O$10,0),0)
Based on position it assigns higher numeric index for lower grade. So the lowest grade subject gets highest priority (1 for A2 to 7 for E1) while upgrading.
=Numeric grade * 100000000
Note: It also meant that grade A1 and E2 would get 0 here and would never be in possibility for upgrade which was another requirement.

If there were more than specified subjects which were eligible for upgrade then we needed to look at 2nd criterion of overall marks.
= Overall Marks * 1000000

If these both matched then we went to third condition.
= SA Marks * 10000

If there was a tie then we'd explore 4th one.
= FA Marks * 100

And then here we checked 5th one.
= XYZ

And if there still was a tie then we then broke it by their order in the spreadsheet and for this distinguishing we create a sliding COUNTIF which subtracted the COUNT from above product sum.
COUNTIFS($F$6:F6,F6,$G$6:G6,G6,$H$6:H6,H6,$J$6:J6,J6)

This gave us a unique priority number for each subject its eligibility order like below:
272403267
Now you can identify each colored group it relates in sequence. It is as if each number comes into picture only if needed until then we do not need to look at it.

And then we simply used LARGE function's built in capacity to see if the combonumber created for subjected was greater than the number returned by LARGE function. e.g.
=LARGE(num_array,2)
returns 2nd largest value from num_array.

Hope this helps you. If something still remains unclear then please ask.

 

[kamaruddinjnv]

Hi Shrivallabha,
Thanks for this nice explanation. Being a teacher I can say that you are a good teacher. I could understand almost formula except numeric code. why data multiplied by 100000000, then 1000000 and so on......., these numbers are assumed nos or any fixed criteria.

Againg thanks for giving attention.
Thanks.

regards
Kamaruddin