Ignoring Blank cells

[Bob Henson]

I'm monitoring cell B34 in a book of 12 monthly sheets labelled Jan to
Dec using the formula

=ROUND(AVERAGE(Janec!B34),0)

If *any* of the B34 cells are empty I get a #DIV/0! error. This doesn't
happen with a normal AVERAGE command where the data are all on one sheet
unless *all* the cells in the range are empty. In order that this can be
a rolling average figure all year round (i.e. when there are blank B34
cells in months as yet uncompleted) I need to get the formula to ignore
one or more blank entries. I tried using AVERAGEIF but I can't get that
to work either - but that may be my incompetence.

Any ideas, please ?

 

[Claus Busch]

Hi Bob,

Am Fri, 06 Dec 2013 16:28:21 +0000 schrieb Bob Henson:

=ROUND(AVERAGE(Janec!B34),0)

If *any* of the B34 cells are empty I get a #DIV/0! error

I cannot reproduce this behavior.
Are the values in B34 calculated values? Do you have an error in one of
the 12 cells?


Regards
Claus B.

 

[Bob Henson]

Hi Bob,

Am Fri, 06 Dec 2013 16:28:21 +0000 schrieb Bob Henson:


I cannot reproduce this behavior.
Are the values in B34 calculated values? Do you have an error in one of
the 12 cells?

They are all calculated values, the result of averaging a column. I
don't think there are any errors, but I will check again to see.

 

[Bob Henson]

Hi Bob,

Am Fri, 06 Dec 2013 16:28:21 +0000 schrieb Bob Henson:


I cannot reproduce this behavior.
Are the values in B34 calculated values? Do you have an error in one of
the 12 cells?


Regards
Claus B.

Ah, cancel the "no errors" message, the monthly sheets that have got no
figures in at all, have got #DIV/0! error messages in the B34 cell. I
assumed that they did not count as "data" and would be ignored - perhaps
this is not the case?

 

[Claus Busch]

Hi Bob,

Am Fri, 06 Dec 2013 19:05:26 +0000 schrieb Bob Henson:

Ah, cancel the "no errors" message, the monthly sheets that have got no
figures in at all, have got #DIV/0! error messages in the B34 cell. I
assumed that they did not count as "data" and would be ignored - perhaps
this is not the case?

no, errors will not be ignored.
But why you got errors in B34? In B34 is also a AVERAGE formula?


Regards
Claus B.

 

[Bob Henson]

Hi Bob,

Am Fri, 06 Dec 2013 19:05:26 +0000 schrieb Bob Henson:


no, errors will not be ignored.
But why you got errors in B34? In B34 is also a AVERAGE formula?

Yes - it is.

 

[Claus Busch]

Hi Bob,

Am Fri, 06 Dec 2013 19:20:12 +0000 schrieb Bob Henson:

Yes - it is.

then you have errors or text into the range that is applied to this
AVERAGE formula.


Regards
Claus B.

 

[Bob Henson]

Hi Bob,

Am Fri, 06 Dec 2013 19:20:12 +0000 schrieb Bob Henson:


then you have errors or text into the range that is applied to this
AVERAGE formula.


Regards
Claus B.

Yes - I need to find a way to make AVERAGIF skip the B34 cells with the
divide by zero error messages. Entering dummy data in the sheets to get
rid of the divide by zero error (it works OK then, I tried it) is not
really an option as it refers to months yet to come and makes a nonsense
of the spreadsheet if I guess ahead. I need to find what the numerical
value of the divide by zero error message is - I've tried using the text
"#DIV/O!" as the criterion, but that doesn't work, but it must have a
numerical value or some value that I can test for. Anyway, I'm going
round in circles now and my brain hurts , so I'm giving it a rest
until tomorrow. Thanks for your help so far.

 

[Claus Busch]

Hi Bob,

Am Fri, 06 Dec 2013 20:19:14 +0000 schrieb Bob Henson:

Yes - I need to find a way to make AVERAGIF skip the B34 cells with the
divide by zero error messages. Entering dummy data in the sheets to get
rid of the divide by zero error (it works OK then, I tried it) is not
really an option as it refers to months yet to come and makes a nonsense
of the spreadsheet if I guess ahead. I need to find what the numerical
value of the divide by zero error message is - I've tried using the text
"#DIV/O!" as the criterion, but that doesn't work, but it must have a
numerical value or some value that I can test for. Anyway, I'm going
round in circles now and my brain hurts , so I'm giving it a rest
until tomorrow. Thanks for your help so far.

into the sheet you can skip the error cells with:
=AVERAGE(IF(ISNUMBER(B1:B10),B1:B10))
You have to modify the range and then enter the formula with
CTRL+Shift+Enter.


Regards
Claus B.

 

[Bob Henson]

Hi Bob,

Am Fri, 06 Dec 2013 20:19:14 +0000 schrieb Bob Henson:


into the sheet you can skip the error cells with:
=AVERAGE(IF(ISNUMBER(B1:B10),B1:B10))
You have to modify the range and then enter the formula with
CTRL+Shift+Enter.

Thanks for that - I was just about to start again, so I'll incorporate
that and see how I go.