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

K

kamaruddinjnv

Board Regular

Joined

Mar 14, 2014

Messages

56

• Mar 16, 2014

• #2

Please see this corrected table for this above question:

2 subjects upgraded SUBJECT Obt. GRADE UPGRADED GRADE **Upgraded Table of Obtained Marks for Upscaling Purpose. Row/ COLUMN- 6 HINDI C B2 D B2 E OVERALL MARKS 62.00 SA 30.00 FA 32.00 XYZ 50.00 7 ENGL B1 A2 ** 80.00 50.00 30.00 83.33 8 MATHS B1 B1 73.00 40.00 33.00 66.67 9 SCIEN E1 E1 29.00 14.00 15.00 23.33 10 S.ST B1 A2 ** 77.00 55.00 22.00 91.67 <tbody> </tbody>
** One example of upgraded marks mentioned here. <tbody> </tbody>

<tbody>
</tbody>

 

Upvote 0

taurean

Well-known Member

Joined

Jun 17, 2011

Messages

2,190

Office Version

1. 365
2. 2016

Platform

1. Windows

• Mar 16, 2014

• #3

Hi Kamaruddin,

Here's one option which should work for you. I have used couple of helper columns which you will have to build as well.

1. In Cell K6 write following formula:
=(F6*1000000+G6*10000+H6*100+J6)*(J6>25)
And copy it down to Cell K10.

2. Then create a simple LOOKUP table in Cells N3:O11 as below:

	N	O
3	GRADE	HIGHER GRADE
4	A2	A1
5	B1	A2
6	B2	B1
7	C1	B2
8	C2	C1
9	D	C2
10	E1	D
11	E2	E2

<colgroup> <col style="WIDTH: 30px; FONT-WEIGHT: bold"> <col style="WIDTH: 68px"> <col style="WIDTH: 87px"></colgroup> <tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4


3. Then in Cell D6 write following formula:
=IF($C$2=0,C6,IF(LARGE($K$6:$K$10,$C$2)<=K6,VLOOKUP(C6,$N$3:$O$11,2,0),C6))

And copy it down to D10. You should get what you want

If you are not able to follow the comments then let me know I will upload the workbook somewhere and post the link here.

 

Upvote 0

K

kamaruddinjnv

Board Regular

Joined

Mar 14, 2014

Messages

56

• Mar 16, 2014

• #4

Thanks Mr shrivallabha, but doing this in the upgrade column i.e in D column in place of upgraded grade #NA is showing. It may be my mistake due to lake of my expertness. so please upload the file and give the link.

Thanks.

Kamaruddin.

 

Upvote 0

taurean

Well-known Member

Joined

Jun 17, 2011

Messages

2,190

Office Version

1. 365
2. 2016

Platform

1. Windows

• Mar 16, 2014

• #5

Here's the link to download:

https://app.box.com/s/uorkzigobdzywrxky9k9

 

Upvote 0

K

kamaruddinjnv

Board Regular

Joined

Mar 14, 2014

Messages

56

• Mar 16, 2014

• #6

Dear Sir,

Thanks lot for try this but some problem is still now. perhaps I was unable to clear it. Now I am trying again to explain it:

Following conditions which is mentioned points wise 1-4 only required when lower grade of two subject or one subject will be tie ( same). But it is first condition that grade will be upgraded from lower grade firstly( it will be firstly start from E1,D,C2....) . condition 1-4 is secondary. according to your formula grade is upgrading according to heighest marks of overall firstly ........ . Point -5 only applicable whenever XYZ marks will be below than 25% . whenever it will applicable in that condition E1 grade of concerned subject will not be upgraded. Hope you will understand the matter from my poor english.

<tbody>
</tbody>

Please download this fille for new explanation-

http://files.kamaruddin.webnode.com/200000311-00f0e01e7f/upgrade.xlsx

Thanks

Kamaruddin.

 

Upvote 0

taurean

Well-known Member

Joined

Jun 17, 2011

Messages

2,190

Office Version

1. 365
2. 2016

Platform

1. Windows

• Mar 17, 2014

• #7

I did not pay attention to all conditions. Guess I forgot my reading glasses Your information is quite clear.

I think in the latest example you've made mistakes at couple of places.

1. The maximum upgrades number is 2 and you've upgraded at 3 places.

2. Within B1 grade subjects it is MATHS which should be upgraded it first qualifies along with ENGLISH as highest overall marks in the tied grade and then in SA grade it scores more than ENGLISH and hence qualifies for upgrade.

I am uploading revised workbook, please download and test it.

https://app.box.com/s/uorkzigobdzywrxky9k9

 

Upvote 0

K

kamaruddinjnv

Board Regular

Joined

Mar 14, 2014

Messages

56

• Mar 17, 2014

• #8

Hi Mr Shrivallabha,

Lot of thanks for your solution. Really appreciate your knowledge of excel. Now it is working according to my requirement. Hope if I will face any problem regarding this formula, I will get help from you again. yes I did mistake to mentioned example of grading at three places instead of two places. After posting I had seen this, but avoiding to upload a another sheet, I had been think you will be able to understand this mistake and you are able to correct it.

I am trying to understand your procedure to solve it, though I could not able to understand your combo number theory, but seeing you easy process I surprise.
Thanks a lot again.

with regards

kamaruddin

 

Upvote 0

taurean

Well-known Member

Joined

Jun 17, 2011

Messages

2,190

Office Version

1. 365
2. 2016

Platform

1. Windows

• Mar 17, 2014

• #9

kamaruddinjnv said:

Hi Mr Shrivallabha,

Lot of thanks for your solution. Really appreciate your knowledge of excel. Now it is working according to my requirement. Hope if I will face any problem regarding this formula, I will get help from you again. yes I did mistake to mentioned example of grading at three places instead of two places. After posting I had seen this, but avoiding to upload a another sheet, I had been think you will be able to understand this mistake and you are able to correct it.

I am trying to understand your procedure to solve it, though I could not able to understand your combo number theory, but seeing you easy process I surprise.
Thanks a lot again.

with regards

kamaruddin

Click to expand...

Great. Thanks for the feedback.

I used a fairly simpler idea which you can maintain quite easily. The combo number is built up in the same sequence as your conditions are. Only grades were non numerical so I converted them to numeric entities using MATCH formula.

So you can see that it is just the numbers you have written are its ingredients I.e. 2 (Numeric grade) 80 (Overall marks) and so on to get something like 280552540.

Once this is done then we just check the number of upgrades applicable. And upgrade those many subjects. Please check Excel help for functions used so as to get the grip of solution. If something is unclear then please ask.

 

Upvote 0

K

kamaruddinjnv

Board Regular

Joined

Mar 14, 2014

Messages

56

• Mar 18, 2014

• #10

Hi Mr Shrivallabha,
One new problem has occur with this formula- suppose in case of tie in all three column F,G &H, then formula done upgrade the grade of all those subjects and in this situation, condition of C2 is failed. I feel this situation will rarely come but I want in that situation only one or two subject/subjects will be upgraded seeing condition of C2. In that case any one/two (according to C2) will be upgraded among those lowest subjects which will be tie. one example given here.
Remaining other result is fine. Hope I am able to convey the proble to you.
Result wanted	In that situation: >if C=1 then ONLY ONE subject will be upgrade among any three subjects.
>if C=2 then ONLY TWO subjects will be upgrade among any three subjects.

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

Now I am unable to upload file. Hope you can check it by put up any same data in any three subject.

with regards.

kamaruddin

 

Upvote 0