# 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

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

#### 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
3
Views
39
Replies
3
Views
126
Replies
4
Views
201
Replies
3
Views
171
Replies
7
Views
2K

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,151,971
Messages
5,767,389
Members
425,410
Latest member
SmittyT

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

### Which adblocker are you using?

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

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