MATCH function; comparing letter grades in columns

[jef]

I am currently comparing the contents on two cells in columns H & I
(for example, cells H10 and I10). The contents are letter grades
(i.e., A, A-, B+, B, etc., through E). I have my function tell me if
there is any difference between the two columns and report the result
(i.e., “no change,” rating decrease,” or “rating increase”) in the
adjoining column, J10. I use the MATCH function, and since it can’t
distinguish between the letter grades, I have placed the letter grades
in cells M2 through M13. My function is as follows:

=IF(MATCH(H10,M$2:M$13,0)=MATCH(I10,M$2:M$13,0),"No
Change",IF(MATCH(H10,M$2:M$13,0)>MATCH(I10,M$2:M$13,0),"Rating
Decrease","Rating Increase"))

I need to modify the function to distinguish between a “range” of
contents in each cell and provide a similar result in the next column/
cell, J10. I want the function to tell me if cell I10 currently
contains any letter grade from A through C (M2:M8) and cell H10
contains the following to provide the corresponding result:

if H10 contains C- through E (M9:M13), then have a result of “Rating
Decrease;”
if H10 contains A through C (M2:M8), then have a result of “No
Change;”

Conversely, if cell I10 currently contains any letter grade from C-
through E (M9:M13) and cell H10 contains the following to provide the
corresponding result:

if H10 contains C- through E (M9:M13), then have a result of “No
Change;”
if H10 contains A through C (M2:M8), then have a result of “Rating
Increase;”

Can anyone please help? I hope this is a simple modification of the
current MATCH function. Thanks in advance.

 

[Ms-Exl-Learner]

I am currently comparing the contents on two cells in columns H & I
(for example, cells H10 and I10).  The contents are letter grades
(i.e., A, A-, B+, B, etc., through E).  I have my function tell me if
there is any difference between the two columns and report the result
(i.e., “no change,” rating decrease,” or “rating increase”) in the
adjoining column, J10.  I use the MATCH function, and since it can’t
distinguish between the letter grades, I have placed the letter grades
in cells M2 through M13.  My function is as follows:

=IF(MATCH(H10,M$2:M$13,0)=MATCH(I10,M$2:M$13,0),"No
Change",IF(MATCH(H10,M$2:M$13,0)>MATCH(I10,M$2:M$13,0),"Rating
Decrease","Rating Increase"))

I need to modify the function to distinguish between a “range” of
contents in each cell and provide a similar result in the next column/
cell, J10.  I want the function to tell me if cell I10 currently
contains any letter grade from A through C (M2:M8) and cell H10
contains the following to provide the corresponding result:

if H10 contains C- through E (M9:M13), then have a result of “Rating
Decrease;”
if H10 contains A through C (M2:M8), then have a result of “No
Change;”

Conversely, if cell I10 currently contains any letter grade from C-
through E (M9:M13) and cell H10 contains the following to provide the
corresponding result:

if H10 contains C- through E (M9:M13), then have a result of “No
Change;”
if H10 contains A through C (M2:M8), then have a result of “Rating
Increase;”

Can anyone please help?  I hope this is a simple modification of the
current MATCH function.  Thanks in advance.

All the things are ok. I can able to imagine your M9:M13 Data which
will be like the below.
A
B
C
D
E

But what is your M2:M8 data?

 

[Hans Terkelsen]

I am currently comparing the contents on two cells in columns H & I
(for example, cells H10 and I10). The contents are letter grades
(i.e., A, A-, B+, B, etc., through E). I have my function tell me if
there is any difference between the two columns and report the result
(i.e., “no change,” rating decrease,” or “rating increase”) in the
adjoining column, J10. I use the MATCH function, and since it can’t
distinguish between the letter grades, I have placed the letter grades
in cells M2 through M13. My function is as follows:

=IF(MATCH(H10,M$2:M$13,0)=MATCH(I10,M$2:M$13,0),"No
Change",IF(MATCH(H10,M$2:M$13,0)>MATCH(I10,M$2:M$13,0),"Rating
Decrease","Rating Increase"))

I need to modify the function to distinguish between a “range” of
contents in each cell and provide a similar result in the next column/
cell, J10. I want the function to tell me if cell I10 currently
contains any letter grade from A through C (M2:M8) and cell H10
contains the following to provide the corresponding result:

if H10 contains C- through E (M9:M13), then have a result of “Rating
Decrease;”
if H10 contains A through C (M2:M8), then have a result of “No
Change;”

Conversely, if cell I10 currently contains any letter grade from C-
through E (M9:M13) and cell H10 contains the following to provide the
corresponding result:

if H10 contains C- through E (M9:M13), then have a result of “No
Change;”
if H10 contains A through C (M2:M8), then have a result of “Rating
Increase;”

Can anyone please help? I hope this is a simple modification of the
current MATCH function. Thanks in advance.
____________________________________

Hi.

See if this modification works:
=CHOOSE(2+SIGN(SIGN(MATCH(I10,M$2:M$13,0)-7.5)-SIGN(MATCH(H10,M$2:M$13,0)-7.5)),"Rating Decrease","No Change","Rating Increase")

The SIGN function can group into 3 groups, against 2 groups for IF

Hans T.

 

[jef]

All the things are ok.  I can able to imagine your M9:M13 Data which
will be like the below.
A
B
C
D
E

But what is your M2:M8 data?

The M2:M8 data is A; A-, B+, B, B-, C+, and C. The M9:M13 data is C-,
D+, D, D- and E.

jef

 

[jef]

I am currently comparing the contents on two cells in columns H & I
(for example, cells H10 and I10).  The contents are letter grades
(i.e., A, A-, B+, B, etc., through E).  I have my function tell me if
there is any difference between the two columns and report the result
(i.e., “no change,” rating decrease,” or “rating increase”) in the
adjoining column, J10.  I use the MATCH function, and since it can’t
distinguish between the letter grades, I have placed the letter grades
in cells M2 through M13.  My function is as follows:

=IF(MATCH(H10,M$2:M$13,0)=MATCH(I10,M$2:M$13,0),"No
Change",IF(MATCH(H10,M$2:M$13,0)>MATCH(I10,M$2:M$13,0),"Rating
Decrease","Rating Increase"))

I need to modify the function to distinguish between a “range” of
contents in each cell and provide a similar result in the next column/
cell, J10.  I want the function to tell me if cell I10 currently
contains any letter grade from A through C (M2:M8) and cell H10
contains the following to provide the corresponding result:

if H10 contains C- through E (M9:M13), then have a result of “Rating
Decrease;”
if H10 contains A through C (M2:M8), then have a result of “No
Change;”

Conversely, if cell I10 currently contains any letter grade from C-
through E (M9:M13) and cell H10 contains the following to provide the
corresponding result:

if H10 contains C- through E (M9:M13), then have a result of “No
Change;”
if H10 contains A through C (M2:M8), then have a result of “Rating
Increase;”

Can anyone please help?  I hope this is a simple modification of the
current MATCH function.  Thanks in advance.
____________________________________

Hi.

See if this modification works:
=CHOOSE(2+SIGN(SIGN(MATCH(I10,M$2:M$13,0)-7.5)-SIGN(MATCH(H10,M$2:M$13,0)-7.5)),"Rating Decrease","No Change","Rating Increase")

The SIGN function can group into 3 groups, against 2 groups for IF

Hans T.

I think this works. Thanks a lot.

jef

 

[shriil]

I think this works.  Thanks a lot.

jef- Hide quoted text -

- Show quoted text -

hI Hans

Was following the discussion. Could you explain the logic behind the
figure "7.5"

 

[Hans Terkelsen]

I think this works. Thanks a lot.

jef- Hide quoted text -

- Show quoted text -

hI Hans

Was following the discussion. Could you explain the logic behind the
figure "7.5"
_______________

Yes, Sanjib.

The outside SIGN splits in 3 cases, giving -1, 0 or 1
but the inside SIGN(s) splits in 2 cases, giving -1 or 1.
According to MATCH from 1 to 7 or MATCH from 8 to 13
That was the two cases Jef wanted to consider, I think.
Hence the SIGN(MATCH...-7.5) comparison

It would be simpler, maybe, to use comparison operators, <>,
instead of the inside SIGN(s).

Hans T.

 

[shriil]

hI Hans

Was following the discussion. Could you explain the logic behind the
figure "7.5"
_______________

Yes, Sanjib.

The outside SIGN splits in 3 cases, giving -1, 0 or 1
but the inside SIGN(s) splits in 2 cases, giving -1 or 1.
According to MATCH from 1 to 7 or MATCH from 8 to 13
That was the two cases Jef wanted to consider, I think.
Hence the SIGN(MATCH...-7.5) comparison

It would be simpler, maybe, to use comparison operators, <>,
instead of the inside SIGN(s).

Hans T.- Hide quoted text -

- Show quoted text -

Thanks for the explanation