Average Based on Criteria

[kumara_faith]

Hi,

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
2	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec		Averager Decrease YTD	Averager Decrease (3M)
3	100	95	85	84	74	65	62							#DIV/0!
4
5
6	Decrease	5%	12%	1%	14%	14%	5%
Sheet1

Cell Formulas
Range	Formula
O3	O3	=AVERAGE((B3/C3)-1,(C3/D3)-1,(D3/E3)-1,(E3/F3)-1,(F3/G3)-1,(G3/H3)-1,(H3/I3)-1,(I3/J3)-1,(J3/K3)-1,(K3/L3)-1,(L3/M3))
C6:H6	C6	=(B3/C3)-1

1) In cell O3, I am trying to calculate in one formula to calculate the average decrease in percentage for the series of sales amount from Jan to Dec. The data will be filled as the months pass and therefore some months will be empty. The average of the decrease from month to month is actually 8%. Is there a way to calculate this in one formula ? Row 6 doesn't actually exist in my data set and its only for reference for this post.
2)In addition, I am also trying to calculate the same in cell P3 but for the last three months of the data. If the data is currently from Jan to Mar, then the correct result will be 9% and if the data is from Jan to Jul then the correct result will be 11%

Appreciate all the help.

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2		Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec		Averager Decrease YTD	Averager Decrease (3M)
3		100	95	85	84	74	65	62							8%	11%
Sheet6

Cell Formulas
Range	Formula
O3	O3	=LET(f,FILTER(B3:M3,B3:M3<>""),AVERAGE(DROP(f,,-1)/DROP(f,,1)-1))
P3	P3	=LET(f,TAKE(FILTER(B3:M3,B3:M3<>""),,-4),AVERAGE(DROP(f,,-1)/DROP(f,,1)-1))

 

[kumara_faith]

Hi Fluff,

That worked. Thank you for your patience and solution, Have a great day ahead.

 

[Fluff]

You're welcome & thanks for the feedback.