How to upgrade the lower grade to their higher level as per given conditions

[nskatoch]

How to upgrade the only one or two grades to their higher level as per condition mentioned in the col. "C" in the below table; i.e
IF A2 THEN A1,
IF B1 THEN A2,
IF B2 THEN B1,
IF C1 THEN B2,
IF C2 THEN C1,
IF D THEN C2,
IF E1 THEN D,
IF E2 THEN E1.

A

B

C

D

E

F

G

H

I

J

K

L

M

R.N.

NAME

UPGRADATION

ENG.

Upgrade

MATHS

Upgrade

SCI.

Upgrade

S.SCI.

Upgrade

COMP.

Upgrade

1

SAM

Upgradation in two subjects

A2

CI

D

CI

B2

2

JOHN

Upgradation in one subject

BI

BI

CI

CI

A2

3

TOM

Upgradation in two subjects

A1

A1

C2

C1

B1

4

JIM

No Upgradation

A1

A2

BI

BI

B1

<tbody>
</tbody>

 

[AliGW]

How is Excel meant to know which one or two subjects to upgrade?

 

[TonyUK72]

How is Excel meant to know which one or two subjects to upgrade?

It looks like A1 is the highest grade & cannot be upgraded and E2 is the lowest grade, all grades from E2 to A2 need to be upgraded so E2 becomes E1 E1 becomes D2 etc.

 

[AliGW]

Tony - how would that apply to the first row? Surely all of those grades could be upgraded?

 

[TonyUK72]

The easiest solution would be to create a table that you can use with a VLOOKUP formula

Ext	Upg
A1	A1
A2	A1
B1	A2
B2	B1
C1	B2
C2	C1
D1	C2
D2	D1
E1	D2
E2	E1

<colgroup><col width="64" span="2" style="*******48pt"> </colgroup><tbody>
</tbody>

Lets say this table resides in columns X & Y, this will give you the range X2:Y11 for your VLOOKUP formula that you will enter into each of the upgrade cells in your table, columns E, G, I, K & M

=IF(D2="","",VLOOKUP(D2,$X$2:$Y$11,2))

You can then copy and paste this formula to the above mentioned cells.

 

[TonyUK72]

Tony - how would that apply to the first row? Surely all of those grades could be upgraded?

Doh, my bad....... totally didn't read the the problem and your answer to it!!!!

 

[nskatoch]

Doh, my bad....... totally didn't read the the problem and your answer to it!!!!

Thank you sir, but this formula not fulfill the condition mentioned in "UPGRATION" column.(i.e. only in two subjects or one subject which has lower grade)

 

[AliGW]

Thank you sir, but this formula not fulfill the condition mentioned in "UPGRATION" column.(i.e. only in two subjects or one subject which has lower grade)

Yes, we get this now, but how are we to know WHICH two subjects to upgrade if more than one could be?

 

[nskatoch]

Yes, we get this now, but how are we to know WHICH two subjects to upgrade if more than one could be?

Thanks for your reply, Sir any one or two subjects which has lower grade will be upgraded as per condition mentioned in column "C" (i.e. UPGRADATION).