Please Help - Average IF with Date.....

[LaurenHancy]

Hello everyone,

I am having problems with the following formular:

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,
AVERAGE(IF('ALL CLOSED'!$T$2:$T$10000=$B3,
IF('JAN - ALL CLOSED DATA'!$D$8:$D$990=C$3,IF(TEXT('ALL CLOSED'!$O$2:$O$10000,"MMM-YY")="Feb-11",'JAN - ALL CLOSED DATA'!$I$2:$I$10000))))))

Can you please see how i am going wrong with this.

Thanks in advance.

 

[LaurenHancy]

Ive just noticed some errors my self but this but it is still not working:

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,
AVERAGE(IF('ALL CLOSED'!$T$2:$T$10000=$B3,
IF('ALL CLOSED'!$D$2:$D$10000=C$3,IF(TEXT('ALL CLOSED'!$O$2:$O$10000,"MMM-YY")="Feb-11",'ALL CLOSED'!$I$2:$I$10000))))))

 

[Aladin Akyurek]

'JAN - ALL CLOSED DATA'!$D$8:$D$990 is not of the same size as other ranges the formula refers to. But, what was the problem you are having?

 

[LaurenHancy]

Thanks very much for your help.

I do have just one question, how do i add the date into this?

=AVERAGEIF('Jan- ALL CLOSED DATA'!$C$8:$C$1000,B2,'Jan- ALL CLOSED DATA'!$BK$8:$BK$1000)

So i would like to add this into the equasion:

(TEXT('ALL CLOSED'!$B$2:$B$10000,"MMM-YY")="Jan-11")

Thank you very much

 

[Aladin Akyurek]

Thanks very much for your help.

I do have just one question, how do i add the date into this?

=AVERAGEIF('Jan- ALL CLOSED DATA'!$C$8:$C$1000,B2,'Jan- ALL CLOSED DATA'!$BK$8:$BK$1000)

So i would like to add this into the equasion:

(TEXT('ALL CLOSED'!$B$2:$B$10000,"MMM-YY")="Jan-11")

Thank you very much

When you need that date-specifying condition, AVERAGEIFS (for multi-codition average) cannot be used. We need to switch to an array-processing formula. And I guess you mean something like...

Control+shift+enter, not just enter:

=AVERAGE(
    IF(TEXT('ALL CLOSED'!$B$2:$B$10000,"MMM-YY")="Jan-11",
    IF('Jan- ALL CLOSED DATA'!$C$2:$C$10000=B2,
      'Jan- ALL CLOSED DATA'!$BK$2:$BK$10000)))

Adjust the ranges to suit (maybe also the sheet names if two different sheet are unintended).