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

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

taurean

Well-known Member
Joined
Jun 17, 2011
Messages
2,179
Office Version
365, 2016
Platform
Windows
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

New Member
Joined
Mar 14, 2014
Messages
49
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

New Member
Joined
Mar 14, 2014
Messages
49
Hi Mr Shrivallabha,
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.
(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

<colgroup><col><col><col><col><col><col><col span="2"><col><col><col></colgroup><tbody>
</tbody>
 

kamaruddinjnv

New Member
Joined
Mar 14, 2014
Messages
49
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.
(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.

<colgroup><col><col><col><col><col><col><col span="2"><col><col><col></colgroup><tbody>
</tbody>
please see uploaded file for more information:

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

with regards
Kamaruddin
 

taurean

Well-known Member
Joined
Jun 17, 2011
Messages
2,179
Office Version
365, 2016
Platform
Windows
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

New Member
Joined
Mar 14, 2014
Messages
49
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
 
Last edited:

taurean

Well-known Member
Joined
Jun 17, 2011
Messages
2,179
Office Version
365, 2016
Platform
Windows
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

New Member
Joined
Mar 14, 2014
Messages
49
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
 

Watch MrExcel Video

Forum statistics

Threads
1,102,274
Messages
5,485,783
Members
407,515
Latest member
franjey

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top