MATCH function; comparing letter grades in columns

  • Thread starter Thread starter jef
  • Start date Start date
J

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.
 
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.
Click to expand...


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?
 
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.
 
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?
Click to expand...

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
 
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.
Click to expand...

I think this works. Thanks a lot.

jef
 
I think this works.  Thanks a lot.

jef- Hide quoted text -

- Show quoted text -
Click to expand...

hI Hans

Was following the discussion. Could you explain the logic behind the
figure "7.5"
 
I think this works. Thanks a lot.

jef- Hide quoted text -

- Show quoted text -
Click to expand...

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.
 
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 -
Click to expand...

Thanks for the explanation
 
Back
Top