AVERAGEIF - Why one works but another doesn't

[Domski]

Hi there,

Just trying to get my head around something, more out of curiosity than anything else.

I'm using this formula to get averages based on 2 criteria:

{=AVERAGE(IF(Leave_Group=B$3,IF(Leave_Days>0,Leave_Days)))}

Which works fine.

I'm not a great fan of nested IF statements so assumed you'd be able to do the same thing using:

{=AVERAGE(IF(AND(Leave_Group=B$3,Leave_Days>0),Leave_Days))}

But it doesn't work.

Can anyone explain why?

 

[GlennUK]

What are the defintions of Leave_Group and Leave_Days precisely?

 

[Domski]

Hi,

Sorry, they're just named ranges that I use.

Leave Group = Leavers!$D$2:$D$3129

Leave Days = Leavers!$N$2:$N$3129

 

[Brian from Maui]

Dom,

I don't know why AND doesn't work, but without the other IF,

{=AVERAGE(IF((Leave_Group=B$3)*(Leave_Days>0),Leave_Days))}

 

[Domski]

Cheers Brian.

Prefer that approach.