Average If in an array formula using Month Function

[hitmanwa]

Any particular reason this array formula won't work? If I remove the or it works fine but I'm trying to get the average for the current month and the previous month. B1=Current Date.



=AVERAGE(IF(MONTH('Q.A.History'!C1:IV1)=or(MONTH(B1),MONTH(B1)-1),'Q.A. History'!C21:IV21))

 

[T. Valko]

Any particular reason this array formula won't work? If I remove the or it works fine but I'm trying to get the average for the current month and the previous month. B1=Current Date.



=AVERAGE(IF(MONTH('Q.A.History'!C1:IV1)=or(MONTH(B1),MONTH(B1)-1),'Q.A. History'!C21:IV21))

If the date in B1 is in the month of January then MONTH(B1)-1 = 0. Is that your intention?

 

[hitmanwa]

No, but I can see that will be a problem too. I'm trying to get an average for a report that's using the current date as the criteria. If today is the 1st then there won't be any data so I wanted it to include the previous month's data. I've tried the formula using the and function and the or function.

Thanks for the quick reply.

 

[barry houdini]

Which version of excel are you using?

 

[hitmanwa]

This machine is running 2000 I went ahead and added an iserror argument and it works. Is that the most efficient way to do this? I guess since there's no data for the current month it won't work.

 

[T. Valko]

This machine is running 2000 I went ahead and added an iserror argument and it works. Is that the most efficient way to do this? I guess since there's no data for the current month it won't work.

Maybe something like this...

Array entered**:

=AVERAGE(IF(DAY(B1)>1,IF(MONTH(Q.A.History!C1:I1)=MONTH(B1),Q.A.History!C2:I2),IF(MONTH(Q.A.History!C1:I1)=MONTH(B1-DAY(B1)),Q.A.History!C2:I2)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Assuming no empty cells within the range.

 

[njimack]

Maybe this (confirm with ctrl+shift+enter)
=AVERAGE(IF(Q.A.History!C1:IV1>=DATE(YEAR(B1),MONTH(B1)-1,1),Q.A.History!C21:IV21))