INTRODUCTION
Whenever we prepare any report in excel, we have two constituents in any report.
The Text portion and the Numerical portion.
But just storing the text and numbers doesn’t make the super reports. Many times we need to automate the process in the reports to minimize the effort and improve the accuracy.
Many functions are provided by the Excel which work on Text and give us the useful output as well. But few problems are still left on which we need to apply some tricks with the available tools.
In this articles , we are going to use the TEXT with IF STATEMENT which is a very practical scenario and is used a lot while preparing reports.
HOW TEXT IS HANDLED IN EXCEL?
TEXT is simply the group of characters and strings of characters which convey the information about the different data and numbers in Excel. Every character is connected with a code [ANSI].
Text comprises of the individual entity character which is the smallest bit which would be found in Excel.We can perform the operations on the strings[Text] or the characters.
Characters are not limited to A to Z or a to z but many symbols are also included in this which we would see in the later part of the article.
TEXT IS AN INACTIVE NUMBER TYPE[FORMAT] IN EXCEL. ANYTHING STORED AS TEXT [NUMBER OR DATE] WON’T RESPOND TO ANY STANDARD FORMULAS OR FUNCTIONS BUT SPECIALLY DESIGNED TEXT FUNCTIONS. [EXCEPTIONS DO OCCUR IN CASE OF NUMBERS]
If we need to make anything inactive, such as Date to be non responding to the calculation, we put it as a text. Similarly if we want to avoid any calculations for a number it needs to be put as a text.
IF STATEMENT WITH TEXT
IF function is one of the most important function in Excel.
Life is full of conditions and decisions. The same option in Excel is fulfilled by the IF FUNCTION.
In this section, let us try to use IF with the TEXT and try to solve many problems.
The learning is best using the examples, so we will learn by discussing the examples.
EXAMPLE : USING IF FOR TEXT AND PERFORM FURTHER ACTION
Let us start with the simple example.
Suppose we have two columns. One column is having the values as YES and NO.
We will put the value PASS if the first column contains YES otherwise NO.
| DECIDING FACTOR | DECISION |
| YES | |
| NO | |
| YES | |
| YES | |
| NO | |
| NO |
We have to take the decision using the IF and checking the presence of TEXT in the first column.
STEPS TO USE IF FOR TEXT AND TAKE DECISION:
- Select the cell in the first decision cell and write the function as =IF(DECIDING FACTOR CELL=”YES”,”PASS”,”FAIL”).
- For our example, we have the deciding factor in E7 so our formula becomes IF(E7=”YES”,”PASS”,”FAIL”)
- The result came out as PASS which is correct.
- Now drag down the formula through the column.
- The result is shown in the picture.
TEXT ALWAYS GOES INSIDE “”. IF TEXT IS NOT INSIDE THE INVERTED COMMAS , IT’LL CREATE AN ERROR.
