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

Nothingdoing079

New Member
Joined
Aug 19, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,870
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Oct 18, 2011
Messages
688
Office Version
  1. 365
Platform
  1. Windows
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)
 

Forum statistics

Threads
1,148,397
Messages
5,746,464
Members
424,021
Latest member
naimathulla

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?

Disable AdBlock

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
Top