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

Hi everyone,

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


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

Scratching my head - feels like a Friday not a Thursday :-)

--
Mifty

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

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:A50,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






--
Mifty


"Mifty" wrote:

Hi everyone,

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


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

Scratching my head - feels like a Friday not a Thursday :-)

--
Mifty

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

=if(iserror(average(P1:p4)),"",average(p1:p4))

"Mifty" wrote:

Hi everyone,

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


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

Scratching my head - feels like a Friday not a Thursday :-)

--
Mifty

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

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?



--
Mifty


"Duke Carey" wrote:

=if(iserror(average(P1:p4)),"",average(p1:p4))

"Mifty" wrote:

Hi everyone,

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


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

Scratching my head - feels like a Friday not a Thursday :-)

--
Mifty

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

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.

"Mifty" wrote:

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?



--
Mifty


"Duke Carey" wrote:

=if(iserror(average(P1:p4)),"",average(p1:p4))

"Mifty" wrote:

Hi everyone,

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


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

Scratching my head - feels like a Friday not a Thursday :-)

--
Mifty

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

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:P40,P1:P4)),"",AVERAGE(I F(P1:P40,P1:P4,FALSE))

Help!
--
Mifty


"Duke Carey" wrote:

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.

"Mifty" wrote:

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?



--
Mifty


"Duke Carey" wrote:

=if(iserror(average(P1:p4)),"",average(p1:p4))

"Mifty" wrote:

Hi everyone,

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


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

Scratching my head - feels like a Friday not a Thursday :-)

--
Mifty

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

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

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



Mifty wrote:

Hi everyone,

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

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

Scratching my head - feels like a Friday not a Thursday :-)

--
Mifty

--

Dave Peterson

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

try

=IF(ISERROR(AVERAGE(IF(P1:P40,P1:P4))),"",AVERAGE (IF(P1:P40,P1:P4)))

"Mifty" wrote:

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:P40,P1:P4)),"",AVERAGE(I F(P1:P40,P1:P4,FALSE))

Help!
--
Mifty


"Duke Carey" wrote:

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.

"Mifty" wrote:

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?



--
Mifty


"Duke Carey" wrote:

=if(iserror(average(P1:p4)),"",average(p1:p4))

"Mifty" wrote:

Hi everyone,

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


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

Scratching my head - feels like a Friday not a Thursday :-)

--
Mifty

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

FANTASTIC !

Thank you so much Duke :-)
--
Mifty


"Duke Carey" wrote:

try

=IF(ISERROR(AVERAGE(IF(P1:P40,P1:P4))),"",AVERAGE (IF(P1:P40,P1:P4)))

"Mifty" wrote:

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:P40,P1:P4)),"",AVERAGE(I F(P1:P40,P1:P4,FALSE))

Help!
--
Mifty


"Duke Carey" wrote:

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.

"Mifty" wrote:

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?



--
Mifty


"Duke Carey" wrote:

=if(iserror(average(P1:p4)),"",average(p1:p4))

"Mifty" wrote:

Hi everyone,

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


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

Scratching my head - feels like a Friday not a Thursday :-)

--
Mifty

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

Hi Dave,

Thanks for replying.

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

Thanks
--
Mifty


"Dave Peterson" wrote:

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

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



Mifty wrote:

Hi everyone,

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

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

Scratching my head - feels like a Friday not a Thursday :-)

--
Mifty

--

Dave Peterson