# MS Excel: #VALUE! error



## U4CX (Sep 26, 2006)

Hi everyone,

I'm stuck on the following problem, it would be great if you guys could help.

Basically I have a column of values with "Y" or "N".

I have a formula "=IF(G4:G12 = "Y", "TRUE", "FALSE")"
So what I want to achieve is that if G4:G12 all contain "Y", the cell will be "TRUE", and if there is one or more "N" present, "FALSE" will be displayed.

The problem is that if I use this formula in anywhere "inside" the actual data range, it will work. But if I put this formula in lines "outside" the data, it will give me a #VALUE! error.

For example, the data is from G4:G12, so I put this formula anywhere between row 4 and row 12, it will work, but on row 100 or row 1 it will not work.

If anyone could help me or tell me what's causing this, it would be appreciated.

Thanks!


----------



## Elkar (Mar 17, 2008)

Your formula is referencing an array and therefore needs to be committed as an array.

Instead of hitting ENTER to commit your formula, use CTRL-SHIFT-ENTER. If done properly, your formula should be enclosed in { }.

Normally, Excel would evaluate your formula as the range G4:G12 being equal to "Y". This doesn't make any sense, and thus why you get an error. By evaluating as an array, Excel treats each part of the range separately, G4="Y", G5="Y", G6="Y", G7="Y" etc....

HTH,
Elkar


----------



## U4CX (Sep 26, 2006)

Actually, after trying it around, my formula doesn't even work.

It will only return T or F when the data on the SAME row is "Y" or "N", it will not look at datas on the other rows. Which is why I get the #VALUE! error because it's looking at the row (but the row doesn't have any data).

So I guess my new question is what would a formula/function be if I wanted to see if all the data in a column is equal to some value.

Y
Y
Y Would return "TRUE"
------------------------------
Y
N
Y Would return "FALSE"

Sorry for the confusion, I'm not good at this.


----------



## U4CX (Sep 26, 2006)

Elkar said:


> Your formula is referencing an array and therefore needs to be committed as an array.
> 
> Instead of hitting ENTER to commit your formula, use CTRL-SHIFT-ENTER. If done properly, your formula should be enclosed in { }.
> 
> ...


Thanks for your response.

I just tried the step you wrote.

My formula now looks like {=IF(G4:G12="Y", "TRUE", "FALSE")}, but the strange thing is, it now only updates according to the first row's element.

For example:

Y <- first row
Y
Y
Y Would give me TRUE
--------------------------
Y <- first row
N
N
N Would also give me TRUE (should be FALSE)
--------------------------
N <- first row
Y
Y
Y Would give me FALSE


----------



## Elkar (Mar 17, 2008)

My apologies! I thought that would have worked. I guess I should have tried it first. 

Ok, this time I did try it and this formula will work:

=IF(SUMPRODUCT(--(G4:G12<>"Y"))>0,"FALSE","TRUE")

This is not an array formula, so no need to ctrl-shift-enter. The array is assumed in the SUMPRODUCT function.

HTH,
Elkar


----------



## U4CX (Sep 26, 2006)

Thank you so much! It worked.


----------

