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

#### Nothingdoing079

##### New Member
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.

### Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

#### AhoyNC

##### Well-known Member
Maybe something like this:
Book1
ABCDEF
1DateProductAmountStart Date1/1/2021
21/5/2021ProdA5End Date3/31/2021
31/8/2021ProdB8ProductProdA
42/3/2021ProdA6Average\$ 5.50
53/4/2021ProdB8
64/5/2021ProdC9
74/6/2021ProdA8
Sheet1
Cell Formulas
RangeFormula
F4F4=AVERAGEIFS(C2:C7,\$B\$2:\$B\$7,\$F\$3,\$A\$2:\$A\$7,">="&\$F\$1,\$A\$2:\$A\$7,"<="&\$F\$2)

#### Crystalyzer

##### Well-known Member
Will this do the trick?

Book12
ABCDEFG
1ProductStart MonthEnd MonthPriceMonthly AverageMonth
2Product A01/01/2131/03/215531/01/21
3Product A01/02/2105/10/2165.528/02/21
4Product A02/03/2108/08/2186.33333331/03/21
Sheet1
Cell Formulas
RangeFormula
F2:F4F2=AVERAGEIFS(\$D\$2:\$D\$4,\$B\$2:\$B\$4,"<="&G2,\$C\$2:\$C\$4,">="&EOMONTH(G2,-1)+1)

Replies
1
Views
532
Replies
3
Views
566
Replies
3
Views
313
Replies
3
Views
59
Replies
5
Views
203

1,181,375
Messages
5,929,585
Members
436,681
Latest member
natalie123vba

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back