Microsoft Windows 7 home premium

I want to writ a formula in Excel, with too many conditions ---. Its for Grading - A1 to one who is 20 - 18.1, A2- 18.1 to 16.1, B1 if 16.1 -14.1, B2 if 14.1 - 12.1, C1 if 12.1 to 10.1, C2 10.1 - 8.1, D 8.1 and 6.5 E1 6.5 - 4.1, else E2..... Hope to find a solution here :-)

To begin with, your conditions overlap. The lower limit of A1 is 18.1 but that is also the upper limit of A2. The same "lower limit vs. upper limit" situation occurs in all of your Grade ranges. You need to define your ranges better.

Assuming you can get your ranges correct, I would use a VLOOKUP.

Start with something like this in A1:B9 -

A B 1 0 E2 2 4.1 E1 3 6.5 D 4 8.1 C2 5 10.1 C1 6 12.1 B2 7 14.1 B1 8 16.1 A2 9 18.1 A1With the grade you are looking for in C1, enter this in D1:

=VLOOKUP(C1, $A$1:$B$9, 2, 1)

This will try to find the value from C1 in A1:A9. Since the

range_lookupargument is TRUE (1), if the value is not found VLOOKUP will find the highest value that is less than the value in C1 and return the value that is in column 2 of thelookup_array(B).

I think I sud reframe my question : I have marks with me and I want that as a result of formula - the grades - A1, A2...etc Format of my worsheet :

Student's Name : Marks - Maths Grade (M) English Grade (E)XYZ 19.5 ? 12.3 ?

I need a formula so that the grade should come for all students. As u said range could be changed as

A1 20 to 18.1

A2 18.09 to 16.1

B1 16.09 to 14.1

B2 14.09 to 12.1

C1 12.09 to 10.1

C2 10.09 to 8.1

D 8.09 to 6.5

E1 6.4 to 4.1

E2 4.09 to 0Thanks for reply.

Did you try my suggestion? I used exactly what I suggested in my previous response and got these results:

19.5 A1 12.3 B2 18.09 A2 3.9 E2You should review the Excel Help files for VLOOKUP so you understand how it works.

VLOOKUP(lookup_value, table_array, col_index_num, range_lookup) =VLOOKUP( C1, $A$1:$B$9, 2, 1) A B C D 1 0 E2 19.5 A1 2 4.1 E1 12.3 B2 3 6.5 D 18.09 A2 4 8.1 C2 3.9 E2 5 10.1 C1 6 12.1 B2 7 14.1 B1 8 16.1 A2 9 18.1 A1

The VLOOKUP formula will "look up" the numerical grade (lookup_value) in first column of the table (table_array) and return corresponding letter grade from the second column(col_index_num). Therange_lookupargument is set to TRUE (1), so it will return the letter grade that corresponds to the highest number that is less thelookup_value.You can put the table anywhere you want, just change the $A$1:$B$9 to match the range where you put the table.

The

lookup_valuerange I used was C1, but you should use the cell references for the cells that contain your numerical grades. Do not use the dollar signs with thelookup_valuerange so you can drag the formula anywhere you want, as I did in the example above.Note: If you are using any version of Excel beyond 2003, you can use more than 7 conditions in an IF statement, but that is very cumbersome.

Mnay thanks for ur reply... I will try this and revert back

Ask Your Question

Weekly Poll

Do you think Microsoft can save the Surface Book lineup?

Discuss in The Lounge

Poll History