Formula Error

[BB]

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(P1070>0,(P1070)+(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:B28>0,B6:B28)) gives
the DIV/0 error message, as December 07 - June 08 not yet entered. Can this
be actually work???

Thank you,

 

[T. Valko]

Try this array formula:

=AVERAGE(IF(ISNUMBER(B6:B28),IF(B6:B28>0,B6:B28)))

 

[BB]

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

 

[T. Valko]

You're welcome. Thanks for the feedback!