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 to post.
Thank you so much for visiting us & taking first step to become awesome in Excel. Please use below links to navigate our site.
Subscribe
