Display least Grade

[Karthik]

Hi All,

I've grades of each student for each subjects in a row from cell 'A' - 'F'
and I want to display their least grade in a subject in cell 'G'. that is If
a person is in C grade in any one of the subject I want "C" to be displayed
in "Least Grade" column. Is there a formula to get the desired result?

Example

Sub - 1 Sub - 2 Sub - 3 Sub - 4 Sub - 5 Sub - 6 Least Gr
John A B A B A C C
Anna B A B A B B
Steven A A A A A
Mary A B A B A C C


Thanks for your help.

 

[Ron Rosenfeld]

Hi All,

I've grades of each student for each subjects in a row from cell 'A' - 'F'
and I want to display their least grade in a subject in cell 'G'. that is If
a person is in C grade in any one of the subject I want "C" to be displayed
in "Least Grade" column. Is there a formula to get the desired result?

Example

Sub - 1 Sub - 2 Sub - 3 Sub - 4 Sub - 5 Sub - 6 Least Gr
John A B A B A C C
Anna B A B A B B
Steven A A A A A
Mary A B A B A C C


Thanks for your help.

If the grades will always be entered in upper case, then the array formula:

=CHAR(MAX(IF(ISTEXT(B2:G2),CODE(B2:G2))))

If some might be lower case, then:

=CHAR(MAX(IF(ISTEXT(B2:G2),CODE(UPPER(B2:G2)))))

This formula must be **array-entered**:

----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl><shift> while hitting <enter>. If you did this
correctly, Excel will place braces {...} around the formula.

--ron

 

[Lars-Åke Aspelin]

If the grades will always be entered in upper case, then the array formula:

=CHAR(MAX(IF(ISTEXT(B2:G2),CODE(B2:G2))))

If some might be lower case, then:

=CHAR(MAX(IF(ISTEXT(B2:G2),CODE(UPPER(B2:G2)))))

This formula must be **array-entered**:

----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl><shift> while hitting <enter>. If you did this
correctly, Excel will place braces {...} around the formula.

--ron

G2 is the cell for the output so the range should be B2:F2

Here is an alternative formula. This is also an array formula.

=CHAR(MAX(CODE(B2:F2&" ")))

Hope this helps / Lars-Åke

 

[Ron Rosenfeld]

G2 is the cell for the output so the range should be B2:F2

In the table example the OP gave, the first grade was in B2. The grades were
labeled Sub1 through Sub6; so unless there are two grades in one cell, the last
grade cannot be G2.

--ron

 

[Ron Rosenfeld]

Hi All,

I've grades of each student for each subjects in a row from cell 'A' - 'F'
and I want to display their least grade in a subject in cell 'G'. that is If
a person is in C grade in any one of the subject I want "C" to be displayed
in "Least Grade" column. Is there a formula to get the desired result?

Example

Sub - 1 Sub - 2 Sub - 3 Sub - 4 Sub - 5 Sub - 6 Least Gr
John A B A B A C C
Anna B A B A B B
Steven A A A A A
Mary A B A B A C C


Thanks for your help.

I neglected to point out that your table above seems to have data in A2:G2
(name in A, and data in B:G, so your Last Grade will probably be in H.

If your data is more complex; e.g. if you have grades like C+ or A-, then the
sorting becomes more complicated and a UDF might be simpler.
--ron