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

 

[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]

[TABLE="class: grid, width: 800, 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][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Ref
[/TD]
[TD]Price
[/TD]
[TD]Price
[/TD]
[TD]Price
[/TD]
[TD]Formula result
[/TD]
[TD]Price
[/TD]
[TD]Formula result
[/TD]
[TD]Price
[/TD]
[TD]Formula result
[/TD]
[TD]Price
[/TD]
[TD]Formula result
[/TD]
[TD]Expected result
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Ref
[/TD]
[TD]2017
[/TD]
[TD]2018
[/TD]
[TD]2019/01
[/TD]
[TD]Price change
[/TD]
[TD]2019/02
[/TD]
[TD]Price change
[/TD]
[TD]2019/03
[/TD]
[TD]Price change
[/TD]
[TD]2019/04
[/TD]
[TD]Price change
[/TD]
[TD]Price change
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/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]4
[/TD]
[TD]AAA
[/TD]
[TD]12
[/TD]
[TD]13
[/TD]
[TD]13
[/TD]
[TD][/TD]
[TD]17
[/TD]
[TD][/TD]
[TD]13
[/TD]
[TD]-23.5%
[/TD]
[TD]17
[/TD]
[TD]0.0%
[/TD]
[TD]30.8%
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]BBB
[/TD]
[TD]12
[/TD]
[TD]13
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]12
[/TD]
[TD]-7.7%
[/TD]
[TD]11
[/TD]
[TD]-15.4%
[/TD]
[TD]-8.3%
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]CCC
[/TD]
[TD]12
[/TD]
[TD][/TD]
[TD]14
[/TD]
[TD]16.7%
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]17
[/TD]
[TD]21.4%
[/TD]
[TD]Ok
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]DDD
[/TD]
[TD]12
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]15
[/TD]
[TD]25.0
[/TD]
[TD]1
[/TD]
[TD]6.7%
[/TD]
[TD]Ok
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]EEE
[/TD]
[TD]0.50
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0.40
[/TD]
[TD]-20.0%
[/TD]
[TD][/TD]
[TD][/TD]
[TD]0.60
[/TD]
[TD]70.0%
[/TD]
[TD]50.0
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9
[/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]

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%).