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!

 

[JBACH92]

Here is what I have now

JAN	=IF(AND(C5<=6,I3<>1),1,0)
FEB	=IF(AND(C5 <= 7, I3 > 2), 1, 0)
MAR	=IF(AND(C5 <= 8, I3 >3), 1, 0)
APR	=IF(AND(C5 <= 9, I3 > 4), 1, 0)
MAY	=IF(AND(C5 <= 10, I3 > 5), 1, 0)	"CURRENT MONTH"
JUN	=IF(AND(C5 < 11, I3 > 6), 1, 0)
JUL	?
AUG	?
SEP	?
OCT	?
NOV	?
DEC	?

<tbody>
</tbody>

 

[JBACH92]

Correction the I3's are supposed to be C5 in the previous post

 

[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
								formula in cell marked #####
								=IF(AND(A8>$O$4-1,A8<$Q$4+1),1,"")
								A8 is the 7 to left of july
								O4 is the 7 to the right of the word months
								Q4 is the 12 to the right of the 7
								formula in O4	=N2-6+1
								formula in N2 (12)	=COUNTA(B2:B101)		(counts how many months in table)

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

 

[JBACH92]

This is for the previous 6 months of the current date?

 

[oldbrewer]

if date is september the anything, - - the last result in table must be august

 

[oldbrewer]

	JAN	12%								12%
	FEB	37%								49%
	MAR	26%			to get the last 6 months			75%
	APR	102%			it will always be the last 6 rows in the table		177%
	MAY	87%								264%
	JUN	16%			the helper column on the right is a running		280%
	JUL	33%			total of the percentages			313%
	AUG	12%								325%
	SEP	82%								407%
	OCT	59%								466%
	NOV	18%			so the 6 month total is the biggest		484%
	DEC	31%			% minus the 7th biggest %			515%
					515%		=LARGE(M7:M50,1)
					280%		=LARGE(M7:M50,7)
					take one from the other and divide by 6

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

 

[oldbrewer]

we are getting toooooo complicated - over tea and biscuits this came to me:-

	JAN	12%
	FEB	37%
	MAR	26%
	APR	102%
	MAY	87%
	JUN	16%	47%
	JUL	33%	50%
	AUG	12%	46%
	SEP	82%	55%
	OCT	59%	48%
	NOV	18%	37%
	DEC	31%	39%			the last one - 39% formula is		=SUM(E41:E46)/6

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

 

[JBACH92]

So essentially I'm still going to have to go back to the whole list of "IF" statements then.

"JAN is cell D36 for instance then" and my result cell (showing the 6 month average) below has:

=IF(MONTH(TODAY())=1, SUM(E41:E46)/6, IF(MONTH(TODAY())=2, SUM(E36, E42:46)/6, IF(MONTH(TODAY())=3 SUM(E36:E37, E43:E46)/6..........IF(MONTH(TODAY())=12, SUM(E40:E45)/6

^The above line is what I was trying to avoid but I'm having a difficult time understanding what you're trying to have me do. And used to be a computer programmer I just don't get along with excel well is all please break it down into what I can put into one cell to compute the previous 6 months from a given date in lamens terms.

 

[oldbrewer]

my post 17 the last 6 are ALWAYS what you want

 

[JBACH92]

Ok, I hope you are not getting frustrated with me because I'm just not following somehow. These values for the months are going to be changing monthly when the other documents are being updated. I'm trying to explain as best as possible, I really am.

Basically this is what is going on:

Doc 1, 2, and 3 have information that is being changed daily.
Doc 4 is the one I am dealing with monthly (this being the one we have been talking about)
In December, I want to open this file and say print and close it. No changing formulas or anything.

From what I understand from your post 17 is you think I always want the last 6 months of the year. No. I am looking for a formula to get the AVERAGE of the previous 6 months values from in December, then in January when that comes. These values and the months values and averages will change every time I open the document to print. But I just want to open this spreadsheet and click print and have that done.