Any way to return an average when figures are spread over multiple months

[Nothingdoing079]

I'm currently trying to work out how I would be able to return the average price of something for a particular month, when I have multiple prices over various date ranges.

For example, I have an agreement for Product A at £5 for the period of 1st Jan to 31 March, the same product at £6 from 1st February to 5th October, and then again at £8 for the period of 2nd March to 8th August.

I'd like to then be able to say for January the average price is £5, for Feb its £5.50 ((£5+£6)/2), March - £6.33 ((£5+£6+£8)/3), April - £7 ((£6+£8)/2), and so on, however can't seem to get a working formula.

I had been trying to use AVERAGEIFS, but the formula I was playing around with struggles to cope if the number is spread over multiple months.

 

[AhoyNC]

Maybe something like this:

Book1
	A	B	C	D	E	F
1	Date	Product	Amount		Start Date	1/1/2021
2	1/5/2021	ProdA	5		End Date	3/31/2021
3	1/8/2021	ProdB	8		Product	ProdA
4	2/3/2021	ProdA	6		Average	$ 5.50
5	3/4/2021	ProdB	8
6	4/5/2021	ProdC	9
7	4/6/2021	ProdA	8
Sheet1

Cell Formulas
Range	Formula
F4	F4	=AVERAGEIFS(C2:C7,$B$2:$B$7,$F$3,$A$2:$A$7,">="&$F$1,$A$2:$A$7,"<="&$F$2)

 

[Crystalyzer]

Will this do the trick?

Book12
	A	B	C	D	E	F	G
1	Product	Start Month	End Month	Price		Monthly Average	Month
2	Product A	01/01/21	31/03/21	5		5	31/01/21
3	Product A	01/02/21	05/10/21	6		5.5	28/02/21
4	Product A	02/03/21	08/08/21	8		6.333333	31/03/21
Sheet1

Cell Formulas
Range	Formula
F2:F4	F2	=AVERAGEIFS($D$2:$D$4,$B$2:$B$4,"<="&G2,$C$2:$C$4,">="&EOMONTH(G2,-1)+1)