Find last value in row that is not a percentage value

[IanPM]

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Price 2017	Price 2018	Price 2019/01	Price change	Price 2019/02	Price change	Price 2019/03	Price change	Price 2019/04	Price change	Price 2019/05	Price change	Price 2019/06	Price change
2	20	22	23	4.5%	25	8.7%	25	0.0%	25	0.0%
3	20	22		0.0%		0.0%		0.0%	25	13.6%
4
5

<tbody>
</tbody>

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

 

[JustynaMK]

Hi Ian,

A small edit to my original formula (I got inspired by Peter's answer):

=SUMPRODUCT(INDEX($A$3:$H$3,1,MAX(IF(ISNUMBER($A$3:$H$3),IF($A$3:$H$3>1,COLUMN($A$3:$H$3))))))

This is an array formula (Ctrl+Shift+Enter) and will also ignore text values. The final result looks like that:

Note that Peter's formula also ignores text values and returns the correct result.

 

[Peter_SSs]

.. I get a name error.

Sounds like you must be using an older version of Excel. Try this one instead. It is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down and then to the other 'Price change' columns.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	Price 2017	Price 2018	Price 2019/01	Price change	Price 2019/02	Price change	Price 2019/03	Price change	Price 2019/04	Price change	Price 2019/05
2	20	22	23	4.5%	25	8.7%	25	0.0%	25	0.0%
3	20	22							25	13.6%
4
Price Change (2)

 

[IanPM]

Hi Peter

Almost perfect.
When the price increases or no change it takes the last value/correct value. However if the price decreases, the formula ignores the last price & continues to use the price before the decrease.
Do you have any suggestions? I assume some kid of MIN/IF statement is needed for decreases?

 

[Peter_SSs]

Hi Peter

Almost perfect.
When the price increases or no change it takes the last value/correct value. However if the price decreases, the formula ignores the last price & continues to use the price before the decrease.
Do you have any suggestions? I assume some kid of MIN/IF statement is needed for decreases?

Sample data and expected result(s) would help.

 

[IanPM]

	A	B	C	D	E	F	G	H	I	J	K	L
1	Ref	Price	Price	Price	Formula result	Price	Formula result	Price	Formula result	Price	Formula result	Expected result
2	Ref	2017	2018	2019/01	Price change	2019/02	Price change	2019/03	Price change	2019/04	Price change	Price change
3
4	AAA	12	13	13		17		13	-23.5%	17	0.0%	30.8%
5	BBB	12	13					12	-7.7%	11	-15.4%	-8.3%
6	CCC	12		14	16.7%					17	21.4%	Ok
7	DDD	12						15	25.0	1	6.7%	Ok
8	EEE	0.50				0.40	-20.0%			0.60	70.0%	50.0
9

<tbody>
</tbody>

Hi Peter

I have some issues to get a file to you via Dropbox

However above table shows the data.
Rows 3,4 & 8 has price reductions up to 2019/03. In 2019/04 (column J) the prices changed but the percentage changes in in column K does not give correct percentages. The expected percentages is in column L

Rows 6 & 7 calculates correctly all the way through. Only difference no reductions in price.

 

[Peter_SSs]

1. What version of Excel are you using?

2. How do you calculate that K7 should be 6.7%? My formula returns -93.3% and that seems logical as a reduction in price from 15 to 1.

 

[IanPM]

Hi Peter

1. Using Excel 2016

2 K7 in my file is 16 then 6.7%, but if 1 then yes -93.3%

 

[IanPM]

https://www.dropbox.com/s/qst6bxxdq55rrrw/Price-changes.xlsx?dl=0



Hi Peter

Dropbox link attached for reference

 

[Peter_SSs]

I haven't looked at the Dropbox file but I do have another question about the price fluctuating. What should each yellow cell here be & any relevant specifics of the calculation to get it?

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	10	20	15		25		10		40		30
Sample

 

[IanPM]

Hi Peter

The yellow cell a straight calculation of the price change percentage from the current price to last / previous price. In the example you send each month has a price, which we o no have, but if this was the case & I use column M as reference then the formula would be L4/J4-1 (-25.0%).