Ignore blanks in cells using =AVERAGE(IF(MONTH...&YEAR...

[gigaenvy]

Hi everyone,

Would anyone be kind and help me figure out how to ignore blanks in this array?

=AVERAGE(IF(MONTH('Employee Data'!$D$3:$D$1935)&YEAR('Employee Data'!$D$3:$D$1935)=MONTH(S15)&YEAR(S15),'Employee Data'!$Z$3:$Z$1935,FALSE))

This works great if every cell in column D has a date, but when I leave something blank it fubars my calc to #Value error.

Any help would be appreciated.

Thanks in advance,

Tom

 

[Marcelo Branco]

Maybe...

=AVERAGE(IF(ISNUMBER('Employee Data'!$D$3:$D$1935),IF(MONTH('Employee Data'!$D$3:$D$1935)&YEAR('Employee Data'!$D$3:$D$1935)=MONTH(S15)&YEAR(S15),'Employee Data'!$Z$3:$Z$1935,FALSE)))

Ctrl+Shift+Enter

M.

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=AVERAGE(IF(ISNUMBER('Employee Data'!$D$3:$D$1935),
  IF('Employee Data'!$D$3:$D$1935-DAY('Employee Data'!$D$3:$D$1935)+1=
    S15-DAY(S15)+1,
     'Employee Data'!$Z$3:$Z$1935)))

 

[gigaenvy]

Control+shift+enter, not just enter:

=AVERAGE(IF(ISNUMBER('Employee Data'!$D$3:$D$1935),
  IF('Employee Data'!$D$3:$D$1935-DAY('Employee Data'!$D$3:$D$1935)+1=
    S15-DAY(S15)+1,
     'Employee Data'!$Z$3:$Z$1935)))

Thanks Aladin - I will test and advise and mark solved at next opportunity.

It seems this was not a challenge for you

Best,

Tom

 

[gigaenvy]

Bingo Aladin! It worked perfectly first try.

 

[Aladin Akyurek]

Bingo Aladin! It worked perfectly first try.

You are welcome. Thanks for providing feedback.