nested formula question

[Harvey Waxman]

I want to average a column (P) only if two conditions are satisfied.
The value in column G must be blank and the value of P must be greater than 0%.

{=AVERAGE(IF(AND($G$13:$G$11000="",$P$13:$P$11000>0%),$P$13:$P$11000))}

The result is always 0% and I can't see what's wrong.

Can you help? I array enter the formula

 

[macropod]

Hi Harvey ,

Try:
{=AVERAGE(IF(($G$13:$G$11000="")*($P$13:$P$11000>0),$P$13:$P$11000))}

 

[Harvey Waxman]

Thanks. That does work but I still can't see why the original formula won't work. It seems much more intuitive.

Hi Harvey ,

Try:
{=AVERAGE(IF(($G$13:$G$11000="")*($P$13:$P$11000>0),$P$13:$P$11000))}

--
Cheers
macropod
[Microsoft MVP - Word]

I want to average a column (P) only if two conditions are satisfied.
The value in column G must be blank and the value of P must be greater than 0%.

{=AVERAGE(IF(AND($G$13:$G$11000="",$P$13:$P$11000>0%),$P$13:$P$11000))}

The result is always 0% and I can't see what's wrong.

Can you help? I array enter the formula

 

[Bob Greenblatt]

I want to average a column (P) only if two conditions are satisfied.
The value in column G must be blank and the value of P must be greater than 0%.

{=AVERAGE(IF(AND($G$13:$G$11000="",$P$13:$P$11000>0%),$P$13:$P$11000))}

The result is always 0% and I can't see what's wrong.

Can you help? I array enter the formula

You have the conditions backwards. try array entering this:
=IF(AND($G$13:$G$11000="",$P$13:$P$11000>0%),average($P$13:$P$11000))

 

[Harvey Waxman]

You have the conditions backwards. try array entering this:
=IF(AND($G$13:$G$11000="",$P$13:$P$11000>0%),average($P$13:$P$11000))

That looks like it should work but I get "FALSE" as the answer

 

[Bob Greenblatt]

That looks like it should work but I get "FALSE" as the answer

then add "", or 0, or whatever you want as the false value preceded by a
comma between the last 2 parentheses.

 

[Harvey Waxman]

then add "", or 0, or whatever you want as the false value preceded by a
comma between the last 2 parentheses.

Hate to be a pain. I added "" and got a blank cell instead of FALSE, but there are thousands of cells that
satisfy the AND condition and I expected to see their average value.

 

[Bob Greenblatt]

Hate to be a pain. I added "" and got a blank cell instead of FALSE, but there are thousands of cells that
satisfy the AND condition and I expected to see their average value.

The only thing I suggest you to try is to restrict the range from over
10K rows to a smaller number which includes both cases. Array enter it
and then highlight portions of the formula in the formula bar and press
F9. This will calculate only the highlighted portioin so you can see
what is happening. And... there is something nagging in the back of my
mind about the number of cells exposed to an array formula. I'll try to
research this further.

 

[Bob Greenblatt]

Hate to be a pain. I added "" and got a blank cell instead of FALSE, but there are thousands of cells that
satisfy the AND condition and I expected to see their average value.

Harvey,

My Bad! The formula will not work as the AND will always return false,
since not ALL of the items are true. I am assuming you want an average
of all the values in P which are greater than 0 if the corresponding
value in G is blank. If so, you'll need a different array formula. Try
this:
=sum(($g13:$g$11000="")*($p$13:$p$11000>0)*($p13:$p$11000))/sum(($g$13:$g$11000="")*($p$13:$p$11000>0))

 

[Harvey Waxman]

Harvey,

My Bad! The formula will not work as the AND will always return false,
since not ALL of the items are true. I am assuming you want an average
of all the values in P which are greater than 0 if the corresponding
value in G is blank. If so, you'll need a different array formula. Try
this:
=sum(($g13:$g$11000="")*($p$13:$p$11000>0)*($p13:$p$11000))/sum(($g$13:$g$11000="")*($p$13:$p$11000>0))

Sorry to make you work so hard. I ended up with this, which does work.

=AVERAGE(IF(($G$18:$G$11000="")*($P$18:$P$11000<0),$P$18:$P$11000))

Many thanks for your continued patience and help on this group.