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,

Just to clarify - would you like to find out what is the most recent price for each row?

For example: row 2 = 25, row 3 = 25

 

[IanPM]

Hi

Not quite
In Row to calculate the percentage in column F it needs to find 23 (column C ).
In row 3 to calculate the percentage in column J it needs to find 22 ( Column B)
I am looking for the last available price that excels the percentages

 

[JustynaMK]

Ahh I think I know what you mean.

You can use the following formula in cell J3 (row 3, price change for 2019/04):

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

Please note that this is an array formula, so it needs to be entered using Ctrl+Shift+Enter. The result will be 12% (which is different to your 13.6% - not sure how this one is calculated).

This is the most crucial part (also an array formula):

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

It finds the latest non-percentage value in cells A3:H3. I simply check if a given value is less than 1 in order to evaluate this condition (check if it works fine for your data set). In order to test it simply add/delete other quarterly values (cells C3, E3, G3) and see how the value changes.

Hope it helps.

 

[IanPM]

Hi

Thankyou

However the array is giving a #value ! error, somehow the formula is detecting some kind text.

Do you have any ideas?

 

[JustynaMK]

Something like that should ignore text values (also an array formula -> Ctrl+Shift+Enter):

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

 

[Peter_SSs]

Does this do what you want?
D2 is copied down then column D formulas copied to columns F, H, J, etc

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

 

[IanPM]

Hi

No I want to find the first non percentage value from previous month's to evaluate the current month price change.
Example J3 must find the last available price which is 22 in B3. The it will do 25/22-1 ( I3/B3-1 )
So the headers are not to be used
The previous array formulas seemed to be on face value it just gave a value error

 

[Peter_SSs]

Hi

No I want to find the first non percentage value from previous month's to evaluate the current month price change.
Example J3 must find the last available price which is 22 in B3. The it will do 25/22-1 ( I3/B3-1 )
So the headers are not to be used
The previous array formulas seemed to be on face value it just gave a value error

Who are you addressing here?
25/22-1 = 13.6% by my calculation - which is what my formula is showing.

 

[IanPM]

Hi Peter

If put this formula: =IF(C2="","",(C2-MAXIFS($A2:B2,$A$1:B$1,"<>Price Change"))/MAXIFS($A2:B2,$A$1:B$1,"<>Price Change")) in cell D2 I get a name error. If I use the formula supplied by Justyna I get a value error