#Value! Same formula, different outcomes

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Howdy,

I have the formula =AVERAGE(IF('09'!C7:N7>0,'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!.
 
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:N7>0,True,False) In cell C2 on your specs
sheet, it will test the value C7>0. When you type the same formula in D2 it
will test D7>0. 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.
 
Howdy,

I have the formula =AVERAGE(IF('09'!C7:N7>0,'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!.
Click to expand...

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
 
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, you’ll 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:N7>0,'11'!C7:N7,FALSE)))

"

I hope this benifits others as well. God Bless.
 
Back
Top