On Oct 29, 3:19 pm, Texas Aggie
wrote:
Howdy,

I have the formula =AVERAGE(IF('09'!C7:N70,'09'!C7:N7)) placed in both cell
C2 and D2 in a worksheet titled "Specs Sheet". Both cell are reference to
sheet "09" in C2 the formula works correctly. In D2 the formula returns
#Value!.

--
Silverbird Designs @www.silverbirddesigns.com

Fighting Texas Aggie Class of 2009

This is an interesting effect. The problem you're seeing is coming
from that fact that you're using '09'!C7:N7 (a range) as the test-
condition of an IF() function. The IF() is testing it properly, but
it takes on an unexpected value.

To see what's happening, simply put the formula "='09'!C7:N7'" in a
cell, I don't know what it *should* do, it doesn't make much sense to
do that, but it is syntactically proper and it does yield a result.
What it does is the interesting thing.

The result of this formula is to pull out of the range C7:N7 the value
which has the same Column coordinate (say, column C) as the cell in
which the formula is entered (column C). If you put this formula into
a cell in a column outside of columns C:N (e.g. A..B,O..IV) , you get
a #Value error, which is what you're seeing. Except, according to my
understanding, you should not be getting the #Value! error when you
evaluate this function from a formula in column D (D2) unless the
contents of '09'!D7 is also #Value! Can you confirm this?

I don't think is the way to do what you're trying to do, Aggie.
It looks like you're trying to average a range of numbers only if the
sum is greater than 0?

try

=IF(SUM('09'!C7:N7)0,AVERAGE('09'!C7:N7))

or if you want an average only if each individual cell is greater than
zero,

=if(MIN('09'!C7:N7)0,AVERAGE('09'!C7:N7))

Gig'em
Brian Herbert Withun