Moving Average based on criteria

  • Thread starter Thread starter Paul H
  • Start date Start date
P

Paul H

Dear all,

Can somebody please help - i have a query about 'averages'.

I have an excel 2000 worksheet with the following array formula: -

=AVERAGE(IF((((MONTH(A6:A500)>MONTH(AU7))*(YEAR(A6:A500)=YEAR(AU7)))*
((MONTH(A6:A500)<MONTH(AV7))*(YEAR(A6:A500)=YEAR(AV7))))*((Z6:Z500)=AS7)*
((H6:H500)=AO8)*((AA6:AA500)=AT7),AB6:AB500,""))

where the values to look up are:

A6:A500 - dates i.e. 01/01/2001
AU7 - is a cell where i state my 'from' date
AV7 - is a cell where i state my 'to' date
AS7 - is a cell where i state if 'closed'
A08 - is a cell where i state if the data is usable 'y'
AT7 - is a cell where i state the priority of the data i.e.
1,2,3
AB6:AB500 - is the data to average based on the above criteria

The formula works as long as both the 'to' & 'from' dates are within
the same year. But, as soon as i go from (say) 2002 to 2003, i get the
wonderfull '#Div/!0'.

I cannot figure out why....... or more importantly, figure out how to
make it work across different years!!!!!!

Any help would be greatly appreciated.

Very Best Regards

Paul H
 
Paul H said:
Dear all,

Can somebody please help - i have a query about 'averages'.

I have an excel 2000 worksheet with the following array formula: -

=AVERAGE(IF((((MONTH(A6:A500)>MONTH(AU7))*(YEAR(A6:A500)=YEAR(AU7)))*
((MONTH(A6:A500)<MONTH(AV7))*(YEAR(A6:A500)=YEAR(AV7))))*((Z6:Z500)=AS7)*
((H6:H500)=AO8)*((AA6:AA500)=AT7),AB6:AB500,""))

where the values to look up are:

A6:A500 - dates i.e. 01/01/2001
AU7 - is a cell where i state my 'from' date
AV7 - is a cell where i state my 'to' date
AS7 - is a cell where i state if 'closed'
A08 - is a cell where i state if the data is usable 'y'
AT7 - is a cell where i state the priority of the data i.e.
1,2,3
AB6:AB500 - is the data to average based on the above criteria

The formula works as long as both the 'to' & 'from' dates are within
the same year. But, as soon as i go from (say) 2002 to 2003, i get the
wonderfull '#Div/!0'.

I cannot figure out why....... or more importantly, figure out how to
make it work across different years!!!!!!

Any help would be greatly appreciated.

Very Best Regards

Paul H
Click to expand...

((MONTH(A6:A500)>MONTH(AU7))*(YEAR(A6:A500)=YEAR(AU7)))*((MONTH(A6:A500)<MON
TH(AV7))*(YEAR(A6:A500)=YEAR(AV7)))
is a very complicated way of determining if your dates fall in the months
(inclusive) between your 'from' and 'to' dates, which appears to be what you
are trying to do. In fact, it fails to do this altogether if there are any
whole years in between 'from' and 'to', which may be your problem.

If you make your 'from' date the first day of the month and your 'to' date
the last day of the month, you could just use
((A6:A500>AU7)*(A6:A500<AV7))

If you must cope with any day of the month in AU7 and AV7, try this:
((A6:A500>DATE(YEAR(AU7),MONTH(AU7),1))*(A6:A500<DATE(YEAR(AV7),MONTH(AV7)+1
,0)))

If this doesn't solve the problem, post back with the formula you've ended
up with.
 
Back
Top