#DIV/0! Error

[Kim]

I am having problems with a spreadsheet I am creating. The spreadsheet looks
something like this.

Average Most Recent
2004 2005 2006 2007 all data
12 Months
Jan 1 2 3 0
Feb 4 5 6 0
Mar 7 8 9 0
Apr 10 11 0 0

The formula I am using for the average of all data is
=sumif(B11:E11,">1")/Countif (B11:E11,">1") This formula seems to work.
However, when there is no data I get a #DIV/0! Error. How can I fix this.

Also, I am still not getting a correct formula to retrieve the most recent
12 months of data. Any help would be appreciated. Thanks in advance.

 

[Dave Peterson]

One way:
=if(countif(b11:e11,">1")=0,"No numbers >=1",
sumif(B11:E11,">1")/Countif(B11:E11,">1"))

 

[Kim]

Thanks for your help. That formula worked! Any ideas for the Most Recent 12
Months? Currently I am using Lookup(maxb11:e11),b11:e11). It works
somewhat. But it puts a 0 in for the most recent 12 months some of the time.
Even if the same info is in the month before. Not sure why.

 

[Dave Peterson]

Nope.

I would think it would be a lot easier to pick out the most recent months if the
data were rearranged.

Date Qty
Jan 1, 2006 3
Feb 1, 2006 5
.....