• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Noob question Excel if query [SOLVED]

K

kumargaurav

New Member
In columns A of excel sheet i have values (1,1,2,2)


in another cell i put up a formula : =IF(A1:A4=1,"yes","no")

i'm getting : 'yes' as output,which is correct.No problems there!!


in another cell i put up a formula : =IF(A1:A4=2,"yes","no")

i'm getting : 'no' as output,which is clearly incorrect.


Can somebody explain what am i missing here.

I would have attached a screenshot if there was an option.
 
Hi Kumar ,


The issue is that you are trying to return an array result in a single cell. If I understand your problem correctly , your values in the cells are :


A1 : 1

A2 : 1

A3 : 2

A4 : 2


What is relevant here is the value in A1 ; A2 through A4 can have any values.


When you put in a formula =IF(A1:A4=1,"yes","no") , you can enter it in two ways - one is a normal formula where you type in the formula , and press the ENTER key. The other way is where you enter the formula as an array formula , which means that after typing it in , you do not press the ENTER key ; instead you press the following 3 keys together - CTRL SHIFT ENTER. This is the way of entering an array formula ; when you do this , you will see that the formula is displayed with curly braces { } around it.


How Excel works with an array formula is different from how it works with a normal formula.


But in the example you have given , it does not matter how you enter the formula ( the results will be different , but neither of them will give you what you are looking for ) ; what matters is what the contents of A1 are ; if A1 has 1 in it , you will get an output of yes , if A1 has 2 in it , you will get an output of no.


If you really want to know if there is a 2 anywhere in the range A1:A4 , try entering the following formula as an array formula , using CTRL SHIFT ENTER :


=IF(OR(A1:A4=2),"yes","no")


Narayan
 
You must log in or register to reply here.
Back
Top