CODEWITHSUNDEEP
excelif-statement
SebbyC
SebbyC

Reputation: 1

If statements in excel not consistent?

In my excel file, I am trying to determine if a set of numbers is greater than the value of another cell.

I have written out an IF statement that only works sometimes depending on the range of the set of numbers. I have been able to replicate the problem but can't understand what is going wrong. I have included an example of my problem.

"B4" is equal to 2

"'Data Sheet'!A1:A10" is a set of 10 numbers from 1 to 10 inclusive.

My first statement (seen below) is working fine:

=IF(B4>'Data Sheet'!A1:A10,"Pass", "Fail")

which returns Fail, as expected.

However, when I change the statement to:

=IF(B4>'Data Sheet'!A6:A10,"Pass", "Fail")

It returns "#VALUE!".

Why does the range of values in the IF statement make a difference to whether it works or not?

Upvotes: 0

Views: 97

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60464

Your formula is an array formula, and it is probably due to serendipity that your first formula is returning what you expect as an answer.

When you enter it into a cell as a non-array formula, which is what you are doing, it will return the value from the column A range that is in the same row as the formula.

Had you entered the first formula in Row 1, it would have returned "Pass" since it would be comparing B4 with A1.

If it is not in a row corresponding to a row in the range, it will return #VALUE.

If you confirm the formula as an array formula, by holding down ctrl + shift while hitting enter, then the formula will return an array of Pass;Fail depending on the relationship of B4 to each of the values in A1:A10. (or A6:A10). The visible value in a single cell will be the result of the first comparison.

So you will need to properly evaluate the array, in order to return whatever it is you want from the formula.

I'm not sure of exactly what you want.

If you want to determine if B4 is greater than any of the numbers in A1:A10, then try (entered normally):

If(B4 > MAX(A1:A10),"Pass","Fail")

If you want something else as a result, you will need to be more specific.

Upvotes: 1

Related Questions