Help with text & number rule! [Solved/Closed]

Report
Posts
5
Registration date
Friday January 3, 2014
Status
Member
Last seen
January 3, 2014
-
Posts
5
Registration date
Friday January 3, 2014
Status
Member
Last seen
January 3, 2014
-
I wonder if anyone would know how to create the following rule in Excel:

IF I enter a specific "text" in cell A, then Excel would insert a corresponding Numerical Value (that I would specify) in cell B. (then I would apply that rule to multiple other cells).

Seems simple enough, but my friend who uses Excel all the time is stumped....

Thx!

AL

5 replies


Al,

The logic for if then is:

=if(logic_test,true, false)

So, with that in mind:
cell B1=if(A1="SOMETEXT",Numerical_value_specified)

Yes, it is simple.

Post back if not what you want, as this satisfies your questions scope/requirement.



//ark
-Moderator/Contributor
Posts
5
Registration date
Friday January 3, 2014
Status
Member
Last seen
January 3, 2014

Perfect! That WAS easy!!

Is it just as easy to add more to that formula?
For instance - I have 3 interchangeable words I typically want to use in cell A, with a different numerical value I would like to programme to show in cell B for the particular word that I type.

Also, if there's no word entered in A (or a completely different word that I haven't specified in the formula) - then I would want to enter in my own numerical value in B for that word.

Yes, Al. You can NEST if then statements like so:

=if(logic_test1,true_logic_test1,if(logictest2,true_logic_test2,false_logic_test2))

This would test logic 1, and only upon false, check logic test 2.

I have done this before (although frowned upon by others):
=IF(A1=1,"ONE",IF(A1=2,"TWO",IF(A1=3,"THREE",IF(A1=4,"FOUR"))))

Have Fun!

//ark
-Moderator/Contributor
Posts
5
Registration date
Friday January 3, 2014
Status
Member
Last seen
January 3, 2014

GREAT!!
After some minor tweaking (reversing your quotation marks)... I got it to work! Amazing. My "Excel Friend" will be more than impressed!

Just one last request to make everything perfect.... How do I indicate in the formula that if I enter "nothing" in Cell A, I get "nothing" in Cell B?

The way it is now.. I get a FALSE word popping into B.
Posts
5
Registration date
Friday January 3, 2014
Status
Member
Last seen
January 3, 2014

Ohhh..... I seemed to have solved the problem.. not sure how...
Now when I type nothing into cell A, I get a 0 in cell B.. which is suitable!

Al, I am happy it is working, I hope I am not doing homework! :)

Take a look at the below statement, and notice the last FALSE statement:
=IF(A1=1,"ONE",IF(A1=2,"TWO",IF(A1=3,"THREE",IF(A1=4,"FOUR",""))))

That says, all false, nothing!

Mark it up as solved if you are happy.
Posts
5
Registration date
Friday January 3, 2014
Status
Member
Last seen
January 3, 2014

Yes everything's working perfectly!

I have nothing typed into the formula concerning a blank space, but when I type nothing into Cell A, I get nothing in Cell B. I don't even get the word FALSE now.

One thing I changed though, was the formatting of Cell B. Changed to Accounting. So now I just get a $ sign, with a -, indicating there's no value present in Cell B. Which is exactly what I wanted.

Sure appreciate the HOURS of time you've just saved me. Thank you!! (I know very little about Excel).

NOW....

Pick a question... any question... perhaps I can be of help in return. :-)