Formula Error
 

Hi,
I have a spreadsheet, one sheet per month (ie November, December etc), which
has this formula to calculate the daily average of sales (column P, and
column U is for account sales, of which there may be not be any for the
month), AVERAGE(IF(P10:P700,(P10:P70)+(U10:U70))) CTRL+SHIFT+ENTER. This is
in cell F72, one row per day, and the 0 part of the formula is there so that
future days are not calculated.

I have a summary page for this current financial year, and each month
contains this formula in cells B6,B8,B10 - B28: ='NOVEMBER 07'!F72 (as per
it's month), however, in Dec 07 - June 08 the cells return the DIV/0 error
message. Then, what i'm tying to do is enter a formula in cell B34 that
gives a daily average over the 12 months(or at this stage, would like to see
the daily avg to date), but this formula =AVERAGE(IF(B6:B280,B6:B28)) gives
the DIV/0 error message, as December 07 - June 08 not yet entered. Can this
be actually work???

Thank you,

Formula Error
 

Try this array formula:

=AVERAGE(IF(ISNUMBER(B6:B28),IF(B6:B280,B6:B28)))

--
Biff
Microsoft Excel MVP


"BB" wrote in message
...
Hi,
I have a spreadsheet, one sheet per month (ie November, December etc),
which
has this formula to calculate the daily average of sales (column P, and
column U is for account sales, of which there may be not be any for the
month), AVERAGE(IF(P10:P700,(P10:P70)+(U10:U70))) CTRL+SHIFT+ENTER. This
is
in cell F72, one row per day, and the 0 part of the formula is there so
that
future days are not calculated.

I have a summary page for this current financial year, and each month
contains this formula in cells B6,B8,B10 - B28: ='NOVEMBER 07'!F72 (as per
it's month), however, in Dec 07 - June 08 the cells return the DIV/0 error
message. Then, what i'm tying to do is enter a formula in cell B34 that
gives a daily average over the 12 months(or at this stage, would like to
see
the daily avg to date), but this formula =AVERAGE(IF(B6:B280,B6:B28))
gives
the DIV/0 error message, as December 07 - June 08 not yet entered. Can
this
be actually work???

Thank you,

Formula Error
 

Biff, you are a legend! Thank you, you have brought an end to my frustration.

"T. Valko" wrote:

Try this array formula:

=AVERAGE(IF(ISNUMBER(B6:B28),IF(B6:B280,B6:B28)))

--
Biff
Microsoft Excel MVP


"BB" wrote in message
...
Hi,
I have a spreadsheet, one sheet per month (ie November, December etc),
which
has this formula to calculate the daily average of sales (column P, and
column U is for account sales, of which there may be not be any for the
month), AVERAGE(IF(P10:P700,(P10:P70)+(U10:U70))) CTRL+SHIFT+ENTER. This
is
in cell F72, one row per day, and the 0 part of the formula is there so
that
future days are not calculated.

I have a summary page for this current financial year, and each month
contains this formula in cells B6,B8,B10 - B28: ='NOVEMBER 07'!F72 (as per
it's month), however, in Dec 07 - June 08 the cells return the DIV/0 error
message. Then, what i'm tying to do is enter a formula in cell B34 that
gives a daily average over the 12 months(or at this stage, would like to
see
the daily avg to date), but this formula =AVERAGE(IF(B6:B280,B6:B28))
gives
the DIV/0 error message, as December 07 - June 08 not yet entered. Can
this
be actually work???

Thank you,

Formula Error
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"BB" wrote in message
...
Biff, you are a legend! Thank you, you have brought an end to my
frustration.

"T. Valko" wrote:

Try this array formula:

=AVERAGE(IF(ISNUMBER(B6:B28),IF(B6:B280,B6:B28)))

--
Biff
Microsoft Excel MVP


"BB" wrote in message
...
Hi,
I have a spreadsheet, one sheet per month (ie November, December etc),
which
has this formula to calculate the daily average of sales (column P, and
column U is for account sales, of which there may be not be any for the
month), AVERAGE(IF(P10:P700,(P10:P70)+(U10:U70))) CTRL+SHIFT+ENTER.
This
is
in cell F72, one row per day, and the 0 part of the formula is there
so
that
future days are not calculated.

I have a summary page for this current financial year, and each month
contains this formula in cells B6,B8,B10 - B28: ='NOVEMBER 07'!F72 (as
per
it's month), however, in Dec 07 - June 08 the cells return the DIV/0
error
message. Then, what i'm tying to do is enter a formula in cell B34
that
gives a daily average over the 12 months(or at this stage, would like
to
see
the daily avg to date), but this formula =AVERAGE(IF(B6:B280,B6:B28))
gives
the DIV/0 error message, as December 07 - June 08 not yet entered. Can
this
be actually work???

Thank you,