IF with Multiple Conditions

Microsoft Windows 7 home premium
July 16, 2010 at 05:44:59
Specs: Windows 7
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 :-)

See More: IF with Multiple Conditions

Report •


#1
July 16, 2010 at 06:25:58
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	A1

With 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_lookup argument 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 the lookup_array (B).


Report •

#2
July 16, 2010 at 21:35:10
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 0

Thanks for reply.


Report •

#3
July 17, 2010 at 05:51:22
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     E2

You 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). The range_lookup argument is set to TRUE (1), so it will return the letter grade that corresponds to the highest number that is less the lookup_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_value range 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 the lookup_value range 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.


Report •

Related Solutions

#4
July 18, 2010 at 04:59:41
Mnay thanks for ur reply... I will try this and revert back

Report •

Ask Question