Dynamic Percent Change Formula

[alhamdan19995]

Hi All,

I am looking for some help with a dynamic percentage change formula. I have quarterly data of purchase price per unit (2019 - 2021) for each product. Some products were not purchased each quarter so I need a formula that would use the most recent purchase in those instances(i.e. purchased in Q1-19 and Q3-19, so calculate the change between those). See example below, what would the most efficient formula that would calculate the change from Q2-20 to Q4-19 for Product A and Q4-20 to Q1-20 for product B?

Purchases per quarter.

	Q1-19	Q2-19	Q3-19	Q4-19	Q1-20	Q2-20	Q3-20	Q4-20
Product A	4	5	3	2		5		2
Product B	3	4			5			2

Thanks!

 

[StephenCrump]

Welcome to the Forum!

Is this of any use?

	A	B	C	D	E	F	G	H	I	J	K	L
1
2		Q1-19	Q2-19	Q3-19	Q4-19	Q1-20	Q2-20	Q3-20	Q4-20		Q4-20	Most recent
3	Product A	4	5	3	2		5		2		-60%	-60%
4	Product B	3	4			5			2		-60%	-60%
5	Product C				10	11					n/a	+10%
6	Product D								3		n/a	n/a
7	Product E			6		12					n/a	+100%
Sheet1

Cell Formulas
Range	Formula
K3:K7	K3	=IF(OR(I3="",COUNT(B3:H3)=0),"n/a",IFERROR(I3/INDEX(B3:H3,MAX(IF(B3:H3<>"",COLUMN(B3:H3)-COLUMN(B3)+1)))-1,"n/a"))
L3:L7	L3	=IF(COUNT(B3:I3)<2,"n/a",INDEX(B3:I3,MAX(IF(B3:I3<>"",COLUMN(B3:I3)-COLUMN(B3)+1)))/INDEX(B3:I3,LARGE(IF(B3:I3<>"",COLUMN(B3:I3)-COLUMN(B3)+1),2))-1)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[alhamdan19995]

Thanks for the help, I tried using your formula but it didn't work. I even tried recreating the table you have to see if it would work there, but for whatever reason it didn't.

See attached an excerpt of the data I have, and sorry if I didn't clarify it better. So I need to calculate the change quarter over quarter for the 2019 - 2021 period for each product and for ones where there were no purchases in the previous quarter, i want to calculate the change against the most recent purchase, if any. For example, for product C, i want the change in price for Q4-19 to be calculated against Q-19 given that there were no purchases in Q2-19 and Q3-19.

Thanks again for the help.

 

[StephenCrump]

I tried using your formula but it didn't work. I even tried recreating the table you have to see if it would work there, but for whatever reason it didn't.

It's not clear what you mean by "didn't work"? I know it's not the solution you're looking for, but it should be something we can adapt.

You don't need to recreate the table. Click where indicated below and you can paste into cell A1 of a blank worksheet. But I think you'll need to array-enter the formulae unless you're using Excel 365. Can you replicate my results if you do this?

It's still not totally clear what results you're looking for. Can you please use XL2BB (which you can download and install here: XL2BB - Excel Range to BBCode) to post some sample data and expected results?

It will also be helpful if you can update your Account details to let us know which Excel version you are using (you'll see from my signature that I'm using 365), as the best solution may vary depending on your version.