Finding the average of multiple (broken up) cells using a function

[JBACH92]

Hi all, just been racking my brain on this one for a while. I am trying to get an average of the last 6 months (from the previous month: ex: if it's November then May-October) of data in this list the only thing I can think of is a whole bunch of nested "IF(MONTH(TODAY())=11, AVERAGE(blah:blah, blah:blah), ...) There has to be an easier way to do this I keep loosing track of the numbers and dates and all.

JAN	12%
FEB	37%
MAR	26%
APR	102%
MAY	87%
JUN	16%
AUG	12%
SEP	82%
OCT	59%
NOV	18%
DEC	31%
12MONTH	40%
6 MONTH	??%

<tbody>
</tbody>

Thank you in advance!

 

[oldbrewer]

I do understand that at all times a cell must display the average of the previous 6 months results. I assume that say the October figure is updated on November 1 st. Therefore it is very easy to sum the last 6 results and divide by 6

jan

25

feb

32

mar

19

apr

24

today is

may

27

jun

26

10/11/2015

jul

31

aug

36

sep

30

oct

32

30.3

so Oct is last monthly result

nov

dec

there is a formula in cellsE6 to E15 (mar to dec)

the one giving 30.3 is

=IF(AND(D13<>"",D14=""),SUM(D8:D13)/6,"")

as soon as you enter the Nov number, the 30.3 will disappear and the 6 month

average to Nov will appear, hence you can open and print

at any time

<colgroup><col span="7"><col><col span="7"></colgroup><tbody>
</tbody>

 

[FormR]

Hi, based on the examples from post #6 here is another option you could try, note: this is using an array function that requires to be confirmed using CTRL+SHIFT+ENTER.

Excel 2012
	A	B	C	D
1	MONTH	A	B	C
2	JAN	12%	10022%	0.35%
3	FEB	37%	16503	0.86%
4	MAR	26%	2800%	0.92%
5	APR	102%	4528%	0.86%
6	MAY	89%	1832%	0.83%
7	JUN	16%	802%	0.12%
8	JUL	33%	3256%	0.03%
9	AUG	33%	41%	0.94%
10	SEP	12%	1247%	0.67%
11	OCT	82%	3111%	0.32%
12	NOV	18%	4015%	0.72%
13	DEC	31%	1218%	0.41%
14	6 MONTH	44%	1715%	0%
Sheet1

Cell Formulas
Range	Formula
B14	{=AVERAGE(IF($A$2:$A$13=TEXT(EOMONTH(TODAY(),{-1,-2,-3,-4,-5,-6}),"MMM"),B2:B13))}
Press CTRL+SHIFT+ENTER to enter array formulas.