Solved

How to test for text in Excel formula

Posted on 2014-04-19
2
208 Views
Last Modified: 2014-04-29
I have a numercal value in A1,
Either Y or N (text cell) is in B1
I want C1 to show the numerical value of A1 if B1 contains the text letter Y
0
Comment
Question by:Bill Golden
2 Comments
 
LVL 37

Accepted Solution

by:
Gerwin Jansen earned 200 total points
ID: 40010812
In cell C1:

=if(B1="Y",A1)
0
 
LVL 24

Assisted Solution

by:SunBow
SunBow earned 100 total points
ID: 40016241
Gerwin Jansen has provided answer as asked.

Question is, however, incomplete, for it does not mention precondition of C1 or potential alternative for B1. Consider case of B1 being blank or numeric or date or some error. Example text of Z or Yes or N/A or y.

This means asker has to have absolute control of everything on the sheets (including formula that may change B1).

To simplify handling an ELSE condition, it is common to include null or blank space text. Example:

    =if(B1="Y",A1,"")            or          =if(B1="Y",A1," ")

A more robust solution would handle condition of B1 being neither Y nor N.

By not handling the other than Y condition, C1 in Excel yields FALSE which is better used as a boolean than a numeric, leading to potential for problems to arise during development.
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today companies are subjected to more-and-more data, and it won't stop any time soon.  But there are obvious opportunities for reducing data, particularly data duplicated among companies.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now