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]

1	JAN	12%											12	months in table
2	FEB	37%
3	MAR	26%				I want the average of the last	6	months		which is months	7	to	12
4	APR	102%
5	MAY	87%
6	JUN	16%				col D is populated with 1's if in chosen date range
7	JUL	33%	1
8	AUG	12%	1
9	SEP	82%	1			AVERAGE =	39.17
10	OCT	59%	1
11	NOV	18%	1
12	DEC	31%	1
						I want the average of	JUN	to	OCT
						these are month	6	to	10		which is	5	months
						average =		40.40
				if this works for you I will post the formulas

<colgroup><col width="64" span="18" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[JBACH92]

You are close, I am looking to be able to open the excel file and it to calculate the past 6 months of data from "todays" (that day's) month. So for example I open the file on Nov 16th then it should compile the average of months JUN to OCT as in the respective previous month. I hope I explained it better and if that is what you were asking then I must be more confused than I thought

 

[JBACH92]

I think where my issue lies is when the current month is for example February it's not as simple as AVERAGE(C?:C?) I am looking for a way to average August to January considering the current month is February still for example.

 

[oldbrewer]

1	JAN	12%
2	FEB	37%
3	MAR	26%	1
4	APR	102%	1
5	MAY	89%	1			let us assume that today is	27/09/2015
6	JUN	16%	1			so the month is		Sep
7	JUL	33%	1			the month number is		9
8	AUG	12%	1			so start month number is	3
9	SEP					so end month number is	8
10	OCT
11	NOV					note - Sep, 9,3,8 are all calculated automatically
12	DEC
						note also - the 1's in column D are calculated automatically
						last 6 months average % is	46.33
						(I changed the may % to avoid a no decimal place answer
						for testing purposes)
						formula giving 46.33 is
						=100*SUMPRODUCT(($C$2:$C$13)*($D$2:$D$13=1))/6
			if the date in J6 (27/09/2015) is replaced by =today() then the last 6 month ave in J17 will always be correct

<colgroup><col span="9"><col><col span="4"></colgroup><tbody>
</tbody>

 

[JBACH92]

Ok, so that does work although there's one issue. I don't want those 1's to be there as in I don't want to have to put them in. Below is a mock of the table "not actual values". All these numbers are references to completely separate documents that are updated monthly. My job is to get the values from each document (doc1 values => col A, doc2 values => col B, doc3 values => col C) and compile them all into this worksheet. I just want to update the documents where I am getting the values from and just print this worksheet without editing it.

MONTH	A	B	C
JAN	12%	10022%	.35%
FEB	37%	16503	.86%
MAR	26%	2800%	.92%
APR	102%	4528%	.86%
MAY	89%	1832%	.83%
JUN	16%	802%	.12%
JUL	33%	3256%	.03%
AUG	33%	41%	.94%
SEP	12%	1247%	.67%
OCT	82%	3111%	.32%
NOV	18%	4015%	.72%
DEC	31%	1218%	.41%
6 MONTH	44%	1715%	0%
12 MONTH	41%	4115%	1%

<tbody>
</tbody>

Saying this is the table I have above; the 6 MONTH cells need to have the average of the past 6 months the aforementioned files are being updated (in other words the current month it would be I have this document open) and I want to do this in a formula without adding any extra cells. Don't mind the values below much because they change monthly I'm worried more about the formula for the 6 month average.

EX: (I'm updating these documents in November):

values used are in blue

MONTH	A	B	C
JAN	12%	10022%	.35%
FEB	37%	16503	.86%
MAR	26%	2800%	.92%
APR	102%	4528%	.86%
MAY	89%	1832%	.83%
JUN	16%	802%	.12%
JUL	33%	3256%	.03%
AUG	33%	41%	.94%
SEP	12%	1247%	.67%
OCT	82%	3111%	.32%
NOV	18%	4015%	.72%
DEC	31%	1218%	.41%
6 MONTH	44%	1715%	0%
12 MONTH	41%	4115%	1%

<tbody>
</tbody>

EX: (I'm updating these documents in February):

values used are in blue

MONTH	A	B	C
JAN	12%	10022%	.35%
FEB	37%	16503	.86%
MAR	26%	2800%	.92%
APR	102%	4528%	.86%
MAY	89%	1832%	.83%
JUN	16%	802%	.12%
JUL	33%	3256%	.03%
AUG	33%	41%	.94%
SEP	12%	1247%	.67%
OCT	82%	3111%	.32%
NOV	18%	4015%	.72%
DEC	31%	1218%	.41%
6 MONTH	44%	1715%	0%
12 MONTH	41%	4115%	1%

<tbody>
</tbody>

 

[oldbrewer]

the 1's can be in column EZ to lose them

 

[JBACH92]

But there's no way to do this without changing the 1's every month? There has to be a way to do set this up so it can just do the calculations based off the current month. "MONTH(TODAY())"

 

[oldbrewer]

I do not type the 1's in - they appear according to your month selection or the current date if you are going for last 6 months.......

 

[JBACH92]

So how are the 1's calculated based off the month then? Where are the formulas for those cells?