Ignoring Blanks on Average(IF

[Stretlow]

Hi there.

I have this formula

{=AVERAGE(IF(Data!B:B=G6,Data!L:L))}

but its including the blank cells in the average calculation.

My question is , is there something i can include to ignore these blank cells?

Many thanks in advance

 

[Fluff]

How about
=AVERAGE(IF((data!B1:B35=G6)*(data!L1:L35<>""),data!L1:L35))

It's a bad idea to use whole column references in array formulae

 

[Marcelo Branco]

For the sake of performance avoid references to entire columns in array formulas - use definite ranges.
Maybe something like this
=AVERAGE(IF(Data!B2:B100=G6,IF(ISNUMBER(Data!L2:L100),Data!L2:L100)))
Ctrl+Shift+Enter

M.

 

[Stretlow]

Thank you to you both they work... and further thanks for the advice on performance.

Stret

 

[Fluff]

You're welcome & thanks for the feedback

 

[Marcelo Branco]

Thank you to you both they work... and further thanks for the advice on performance.

Stret

You're welcome.

By the way, since AVERAGEIF ignores blank cells you can use a regular formula
=AVERAGEIF(Data!B2:B100,G6,Data!L2:L100)

M.