Need blank if no values in range getting value at the mo'

  • Thread starter Thread starter Mifty
  • Start date Start date
M

Mifty

Hi everyone,

Could anyone help and tell me what's the matter with this please?


=if(p1:p4<>,"",AVERAGE(IF(P1:P4>0,P1:P4,FALSE))

Scratching my head - feels like a Friday not a Thursday :-)
 
Oops sorry should have switched brain on first

I keep handy formulas on a spreadsheet which I dip into and then alter the
formula to fit. I just read the note with this one and the original formula
=AVERAGE(IF(A1:A5>0,A1:A5,FALSE)) which averages values above 0 in a range
says to enter as an array.

When I follow the instructions it works and doesn't need the if bit at the
front!

Sorry
 
Hi Duke,

Can I add iserror to an array formula?

Still having problems! When I use the original formula as an array and there
is no data I get a #DIV/0 error.

The formula you gave doesn't have the >0 bit, is that why it has to be
entered as an array?
 
Yes, the >0 test requires the array, and yes, you can add the iserror() to an
array formula.

The > 0 test will exclude zero VALUES and negative values from the average,
but if you're only worried about BLANK cells, then my non-array formula will
work.
 
Hi Duke,

I want cells with zero in to be ignored so I do need it to be an array. I've
been trying in vain to add the iserror bit but get message back re ( ) - then
when I add enough of these I get a message saying there is an error in the
formula.

Here's one of my tries

=IF(ISERRORAVERAGE(IF(P1:P4>0,P1:P4)),"",AVERAGE(IF(P1:P4>0,P1:P4,FALSE))

Help!
 
If you're just trying to stop the division by 0 error:

=if(count(p1:p4)=0,"",average(p1:p4))
 
Hi Dave,

Thanks for replying.

Duke's last post sorted the problem when I remembered (again) to add as an
array!

Thanks
 
Back
Top