Grade compare two non blank cells with alphabets and report trend

[ukbulldog001]

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.

Grade Table:

Book1
	B	C	D	E	F	G	H	I	J	K	L	M
1	JAN	FEB	MAR	APR	MAY	JUN	JUL	AUG	SEP	OCT	NOV	DEC
2					B	B
Sheet1

Used below formula, but with error.
Trend Result:

Book1
	P
1	TREND
2	#VALUE!
Sheet1

Cell Formulas
Range	Formula
P2	=IF(OFFSET(B2:M2,0,COUNTA(B2:M2)-2,1,1)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:

Book1
	P	Q	R
4	CHAR MAP USED	CHAR	Used for
5	ALT+0233		for increment
6	ALT+0234		for decrement
7		n	n for equal
Sheet1

will be using wingding format in the resulting cell.

A simple formual would be:

Book1
	R
1	TREND
2	n
Sheet1

Cell Formulas
Range	Formula
R2	=IF(F2F2>G2,"é","n"))

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

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

 

[Eric W]

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.

 

[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","é")

 

[ukbulldog001]

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.

 

[Eric W]

Happy to help.