• 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.

Easy one here.....

R

rjwalters

New Member
I guess I am not thinking like excel, but I am trying to learn this formula writing. I want H3 to show either 3 or 10 depending upon the answer in F3. If they put N in F3 I want H3 to return 10, if the put Y in H3 I want it to return 3.


I tried it with an if statement =if(F3=Y,3),(F3=N,10)... am I even close to correct.. I am writing the formula in H3.
 
Hi ,


One solution has already been posted , provided you want 10 and 3 to be text values ; if not , you will have to change the posted solution ; I'll just add some explanation.


Writing a correct formula is just a matter of expressing the logic in any language ; for example , if we write down your formula in simple English , we would say :


If F3 contains "N" ( or F3 is equal to "N" ) , this cell ( since you are entering the formula in H3 ) should get the value 10 ; if F3 contains "Y" , this cell should get the value 3.


The syntax of the IF statement is :


=IF(condition to be checked,what is to be done if condition is TRUE, what is to be done if condition is FALSE)


Here the condition to be checked is : F3="N" ; you can also use F3="Y".


what is to be done if condition is TRUE is : 10


what is to be done if condition is FALSE is : 3


Hence , one form of the formula would be :


=IF(F3="N",10,3)


Another form could be :


=IF(F3="Y",3,10)


In both the above cases , we are assuming that if F3 does not contain "N" , it will contain "Y" and vice-versa. Try both the above formulae by entering any character other than "Y" and "N" in F3.


If you specifically want to check for only "Y" and "N" , you can have something like this :


=IF(F3="N",10,IF(F3="Y",3,"Invalid Data"))


Note your formula had problems with the following :


1. Use of Y and N instead of "Y" and "N" ; checking for a text value is done by enclosing the text within double quotes


2. Use of additional parentheses.


Narayan
 
OK so I tried the formulas. The first one worked but it always returned something. I had to leave the cell blank if there was no data.

So the second longer formula worked, I just changed invalid data to " ".


What did not happen is that it did not read the "Y" or "N" in a sentence. The type of data that will be in the cell would be- N, user not allowed, or Y user is allowed. I thought the formula would read the Y or N in the sentence and create an output off of that.....any thoughts
 
Hi ,


When you use the = sign , you are testing for exact equality.


Thus if you have the text "N, user not allowed" in a cell , checking this against "N" will return FALSE , not TRUE.


If you want to just check if the "Y" or "N" is present as the first character in the cell , you will have to use something like this :


=IF(LEFT(F3,1)="N",10,IF(LEFT(F3,1)="Y",3,""))


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