# Thread: Grade compare two non blank cells with alphabets and report trend Thanks:  1 Post #5286972 (1) Likes:  1 Post #5286972 (1)

1. ## Grade compare two non blank cells with alphabets and report trend

Dear MrExcelites,

Greetings, I need a formula to compare the last and the last second cell and report whether it has increased or decreased or stays the same.

BCDEFGHIJKLM
1JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC
2BB

Sheet1

Used below formula, but with error.
Trend Result:
P
1TREND
2#VALUE!

Sheet1

Worksheet Formulas
CellFormula
P2=IF(OFFSET(B2:M2,0,COUNTA(B2:M2)-2,1,1)<OFFSET(B2:M2,0,COUNTA(B2:M2)-2,1,2),"ê",IF(OFFSET(B2:M2,0,COUNTA(B2:M2)-2,1,1)>OFFSET(B2:M2,0,COUNTA(B2:M2)-2,1,1),"é","n"))

Char used alt codes in formula:
PQR
4CHAR MAP USEDCHARUsed for
5ALT+0233éé for increment
6ALT+0234êê for decrement
7nn for equal

Sheet1

will be using wingding format in the resulting cell.

A simple formual would be:
R
1TREND
2n

Sheet1

Worksheet Formulas
CellFormula
R2=IF(F2<G2,"ê",IF(F2>G2,"é","n"))

but I need it to compare last two non blanks in a year.

PS: tried using aggregate, index too but didnt succeed.

2. ## Re: Grade compare two non blank cells with alphabets and report trend

Consider:

=CHOOSE(SIGN(LOOKUP(2,1/(B2:M2<>0),B2:M2)-LOOKUP(2,1/(B2:M2<>0),A2:L2))+2,"ê","n","é")

This assumes there are no gaps between the last and second to last values.

3. ## Re: Grade compare two non blank cells with alphabets and report trend

Sorry, I missed that you are using letters instead of numbers, try:

=CHOOSE(SIGN(CODE(LOOKUP(2,1/(B2:M2<>0),B2:M2))-CODE(LOOKUP(2,1/(B2:M2<>0),A2:L2)))+2,"ê","n","é")

4. ## Re: Grade compare two non blank cells with alphabets and report trend

Originally Posted by Eric W
Sorry, I missed that you are using letters instead of numbers, try:

=CHOOSE(SIGN(CODE(LOOKUP(2,1/(B2:M2<>0),B2:M2))-CODE(LOOKUP(2,1/(B2:M2<>0),A2:L2)))+2,"ê","n","é")
Thanks a lot @eric-w works perfectly.

5. ## Re: Grade compare two non blank cells with alphabets and report trend

Happy to help.