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

Hi,

These two functions will act differently, although I'm not sure why #Value!
appears without knowing the content of the cells being referenced.

When you type in =IF('09'!C7:N70,True,False) In cell C2 on your specs
sheet, it will test the value C70. When you type the same formula in D2 it
will test D70. Because you are referencing a range, ='09'!C7:N7 will give
you an item from that range, indexed by the column, whereas
=Average('09'!C7:N7) will give you the average of the range. I am guessing
that the #Value! error is due to the value in D7.

If you are not trying to test individual values being 0 then let us know
what you are trying to test and someone will be able to help with a formula.
If this is what you were trying to do then try looking at D7 to see if the
error is coming from there.

Sean.
--
(please remember to click yes if replies you receive are helpful to you)


"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

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

Thanks for the responces unfortunatly the problem stands unresolved

I tryed the formula " ='09'!C7:N7 " to make sure there is a value, But get
this. There is a value for the range as long as I place formula in columns A
through N, columns O and beyond give me a #Value!. What do you make of that?

I think this might be a glitch with Excel
--
Silverbird Designs @ www.silverbirddesigns.com

Fighting Texas Aggie Class of 2009


"Brian Herbert Withun" wrote:

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

Can you post some of your data so we could take a look?


--JP


On Oct 31, 9:17 am, Texas Aggie
wrote:
Thanks for the responces unfortunatly the problem stands unresolved

I tryed the formula " ='09'!C7:N7 " to make sure there is a value, But get
this. There is a value for the range as long as I place formula in columns A
through N, columns O and beyond give me a #Value!. What do you make of that?

I think this might be a glitch with Excel
--
Silverbird Designs @www.silverbirddesigns.com

Fighting Texas Aggie Class of 2009

Thanks to our mutual friend Chip Pearson, he has answers my question. Here is
what he had to say about the issue.

"Your formulas need some work. First of all, they are array formulas, which
means that you *must* press CTRL SHIFT ENTER rather than just ENTER when you
first enter the formula and whenever you edit it later (not doing so screws
up the references and is likely the cause of your #VALUE errors). If you do
this properly, Excel will display the formula in the formula bar enclosed in
curly braces { }. Next, if you take an average of an empty range, youll get
a #DIV/0 error. Thus, you need to test whether there is data in the range to
begin with and only then should you compute the average. The array formula
below uses COUNT to see if there is any data in €˜11!C7:N7. If COUNT is 0,
there is no data, so it returns the text €œn/a€� (you can change the €œn/a€� to
whatever you want to display in the case of an empty range). If there is
data, it is fed into the AVERAGE function, as long as that data is 0.

If you change your formula to the following, everything should work as you
expect.



=IF(COUNT('11'!C7:N7)=0,"n/a",AVERAGE(IF('11'!C7:N70,'11'!C7:N7,FALSE)))

"

I hope this benifits others as well. God Bless.

--
Silverbird Designs @ www.silverbirddesigns.com

Fighting Texas Aggie Class of 2009


"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