Find last value in row that is not a percentage value

[IanPM]

[TABLE="class: grid, width: 600, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[TD]K
[/TD]
[TD]L
[/TD]
[TD]M
[/TD]
[TD]N
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Price 2017
[/TD]
[TD]Price 2018
[/TD]
[TD]Price 2019/01
[/TD]
[TD]Price change
[/TD]
[TD]Price 2019/02
[/TD]
[TD]Price change
[/TD]
[TD]Price 2019/03
[/TD]
[TD]Price change
[/TD]
[TD]Price 2019/04
[/TD]
[TD]Price change
[/TD]
[TD]Price 2019/05
[/TD]
[TD]Price change
[/TD]
[TD]Price 2019/06
[/TD]
[TD]Price change
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]20
[/TD]
[TD]22
[/TD]
[TD]23
[/TD]
[TD]4.5%
[/TD]
[TD]25
[/TD]
[TD]8.7%
[/TD]
[TD]25
[/TD]
[TD]0.0%
[/TD]
[TD]25
[/TD]
[TD]0.0%
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]20
[/TD]
[TD]22
[/TD]
[TD][/TD]
[TD]0.0%
[/TD]
[TD][/TD]
[TD]0.0%
[/TD]
[TD][/TD]
[TD]0.0%
[/TD]
[TD]25
[/TD]
[TD]13.6%
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I need the price changes to calculate of the last price value. Data example
In Row 2 the price changed 4.5% from 2019/01 to 2018 & 8.7% in 2019/02 from 2019/01
In row 3 the first price change in column I - 2019/04 at 13.6%.

Question what formula can I use the find the last value which is 22 in 2018 (column B ) & exclude all the percentages?

Thank you

IanPM

 

[Peter_SSs]

OK, got it at last (I hope.)

Excel Workbook
	B	C	D	E	F	G	H	I	J	K	L	M
1	Price	Price	Price	Price change	Price	Price change	Price	Price change	Price	Price change	Price 2019/05	Price change
2	2017	2018	2019/01		2019/02		2019/03		2019/04
3
4	12	13	13	8.3%	17	30.8%	13	-23.5%	17	30.8%
5	12	13					12	-7.7%	11	-8.3%
6	12		14	16.7%					17	21.4%
7	12						15	25.0%	1	-93.3%
8	0.5				0.4	-20.0%			0.6	50.0%
9
10	10	20	15	50.0%	25	66.7%	10	-60.0%	40	300.0%	30	-25.0%
Price Change (4)

 

[IanPM]

Hi Peter

For E4 the formula seems just be extended to include C4. But it sees to work well for al cells.

Thank you very much!

 

[Peter_SSs]

Hi Peter

For E4 the formula seems just be extended to include C4.

Yes, that was a mistake, sorry. Corrected version, still copied to all the other relevant columns:

Excel Workbook
	B	C	D	E	F	G	H	I	J	K	L	M
1	Price	Price	Price	Price change	Price	Price change	Price	Price change	Price	Price change	Price 2019/05	Price change
2	2017	2018	2019/01		2019/02		2019/03		2019/04
3
4	12	13	13	0.0%	17	30.8%	13	-23.5%	17	30.8%
5	12	13					12	-7.7%	11	-8.3%
6	12		14	16.7%					17	21.4%
7	12						15	25.0%	1	-93.3%
8	0.5				0.4	-20.0%			0.6	50.0%
9
10	10	20	15	-25.0%	25	66.7%	10	-60.0%	40	300.0%	30	-25.0%
Price Change (4)

 

[IanPM]

Perfect!

 

[Peter_SSs]

Perfect!

Good news! Glad we got there in the end.