Value Comparison Formula

Excel 2003. We are capturing grades for site reviews based on the scale listed below. On the report we need to indicate how the current grade compares to the grade from the previous period.

If the grade improves or declines within the same letter grade, it is marked as slightly up or slightly down. For example, B - to B would be considered slightly up.

If the grade improves or declines outside the same letter grade, it is marked as up or down. For example, C- to B would be considered up.

The grades are entered using actual values. For example, site one has a grade of 93.5, site 2 81.5 and so on. I use a lookup to convert the score to a letter grade based on the table above.

The comparison can be done using the scores (Week 1, Site 1 93.5; Week 2 Site 1 89, etc). However, I have been unable to find an efficient method to do this.

We are currently using this formula.

=IF(AND(C3="A",OR(G3>="B",G3="C",G3="D",G3="B-")),IF(G3="A-","Down","Up"),IF(AND(C3="B",OR(G3>="C",G3="D-",G3="D+")),IF(G3="F","Down","Up"),IF(AND(C3="C",OR(G3>="D",G3="D-",G3="D+")),IF(G3="F","Down","Up"),IF(AND(C3="B",OR(G3<"B")),IF(G3="F","Up","Down"),IF(AND(C3="C",OR(G3<"C")),IF(G3="F","Up","Down"),IF(AND(C3="D",OR(G3<"D")),IF(G3="F","Up","Down"),IF(C3=G3,"No Change","")))))))

However, it is not very efficient and does not encompass all possible permutations. What other method could be used?

Any assistance is greatly appreciated.

If you add a third column beside your lookup table, you could put ranking numbers there, starting at 1 for F, and ending at 13 for A+. Assuming the second and third columns had a range name of RankTable, then you could try this:

=if(vlookup(c3,ranktable,2,false)>vlookup(g3,ranktable,2,false),"Up",if(vlookup(c3,ranktable,2,false)<vlookup(g3,ranktable,2,false),"Down","Stable")

I see I missed the part about improvements within the same letter grade being slightly up or down. The same idea could be used by either just making all the three "same letter grades" the same number (D-,D,D+ would all be 2), or adding another column with this pattern and adding another test layer in the formula to look for Slightly Up and Up conditions.

Post back for which part of this whole suggestion confused you the most, and by then I might have time to flesh it out more.

Omar:

I see I missed the part about improvements within the same letter grade being slightly up or down. The same idea could be used by either just making all the three "same letter grades" the same number (D-,D,D+ would all be 2), or adding another column with this pattern and adding another test layer in the formula to look for Slightly Up and Up conditions.

Post back for which part of this whole suggestion confused you the most, and by then I might have time to flesh it out more.

I used the following formula: =IF(VLOOKUP(BC2,RankTable,3)>VLOOKUP(BD2,RankTable,3),"Up",IF(VLOOKUP(BC2,RankTable,3)<VLOOKUP(BD2,RankTable,3),"Down","No Change"))

It works fine for the "Up" and "Down" comparison. I am not quite sure how you meant to incorporate the interim "Slightly Up", "Slightly Down" comparison. Here is the table I used.

That will work if the BC2 and BD2 cells are the numeric scores.

The next part depends on an answer to something I'm not clear on. Either way, you would make changes to your formula, but it would be done in slightly different ways.

Option 1:

Did you mean that a change from C to C+ would result in "slightly up", but a change from C+ to B- would be "up" (because of the change in letter)?

Add another column to RankTable with F being 1, all three D's being 2, ending with the A's being 5.

=IF(VLOOKUP(BC2,RankTable,3)>VLOOKUP(BD2,RankTable,3),IF(VLOOKUP(BC2,RankTable,4)>VLOOKUP(BD2,RankTable,4),"Up","Slightly Up),IF(VLOOKUP(BC2,RankTable,3)<VLOOKUP(BD2,RankTable,3),IF(VLOOKUP(BC2,RankTable,4)<VLOOKUP(BD2,RankTable,4),"Down","Slightly Down"),"No Change"))

Option 2:

If the change from C- to C is "slightly up", but C- to C+ is "up" (because of the two step difference).

No need for the additional column.

=IF(VLOOKUP(BC2,RankTable,3)>VLOOKUP(BD2,RankTable,3),IF(VLOOKUP(BC2,RankTable,3)>(VLOOKUP(BD2,RankTable,3)+1),"Up","Slightly Up),IF(VLOOKUP(BC2,RankTable,3)<VLOOKUP(BD2,RankTable,3),IF(VLOOKUP(BC2,RankTable,3)<(VLOOKUP(BD2,RankTable,3)+1),"Down","Slightly Down"),"No Change"))

I didn't test these, especially the Option 2. I think I got this right, but you may need to adjust the "+1" to get the answer you were expecting.

I used Option 1. Here is my formula:

=IF('Data Entry Sheet'!$BD2="Single Review","Single Review",IF(VLOOKUP('Data Entry Sheet'!$BC2,RankTable,3)>VLOOKUP('Data Entry Sheet'!$BD2,RankTable,3),IF(VLOOKUP('Data Entry Sheet'!$BC2,RankTable,4)>VLOOKUP('Data Entry Sheet'!$BD2,RankTable,4),"Up","Slightly Up"),IF(VLOOKUP('Data Entry Sheet'!$BC2,RankTable,3)<VLOOKUP('Data Entry Sheet'!$BD2,RankTable,3),IF(VLOOKUP('Data Entry Sheet'!$BC2,RankTable,4)<VLOOKUP('Data Entry Sheet'!$BD2,RankTable,4),"Down","Slightly Down"),"No Change")))

It works great. Thanks!

I also used the following for when only one step is needed. It only uses the first column of the table.

=IF(LOOKUP($C$18,RankTable)>LOOKUP($D$18,RankTable),"Up",IF(LOOKUP($C$18,RankTable)<LOOKUP($D$18,RankTable),"Down","No Change"))

Good thing you used option 1, as option 2 had a slight mistake in it. Glad it got you going. Looking at that formula, it sure feels like there has to be a better way.