- 1 Greater/smaller than or equal to~, greater/smaller than~, equal to~
- 2 Define ‘including~’/Use the wildcard character ‘*’
- 3 Codes of text and symbols/CHAR function
- 4 Compare dates (Date form and text form)
Greater/smaller than or equal to~, greater/smaller than~, equal to~
When using the IF function, we sometimes want to include complicated conditions such as ‘greater than…‘ or ‘smaller than or equal to…’. Let us see how we can define these conditions correctly in the formula.
Example of use) How to use the comparison operator
Below are the results of evaluations carried out by the Human Resources department. We would like to use the IF function to create a condition where ‘if the evaluation score is more than 350 points, display a “pass”‘ and if not, ‘a “fail”‘.
We will explain below how to include the comparison operator (greater than or equal to~) in the formula of the IF function.
In Mathematics we express it like ‘D4≧350‘, but in Excel, we begin with the ‘= (equal)’ sign.
We express ‘smaller than or equal to…’ like ‘D4<=350’.
Below is a list of comparison operators that we use often:
【Symbols of equality and inequality】
|<||Smaller than the selected number|
|<=||Equal to or smaller than the selected number|
|>||Bigger than the selected number|
|>=||Equal to or bigger than the selected number|
|<>||Not equal to selected number (besides/other than)|
|=||Equal to selected number|
Define ‘including~’/Use the wildcard character ‘*’
Other than comparison operators, we might want to use ‘*’ and ‘?’ to define cases such as ‘including the text/word …’.
For example, ‘if the corresponding word is included in the beginning’, input ‘○○*’, and ‘if there is 1 different letter in the corresponding word’, we can input ‘○○?○○’.
Example of use) Wildcard character
This year, a company has decided to provide a summer bonus for only the Sales department. Therefore, in column D, they have created the table below to show which employees can get the summer bonus. If the employee ‘is a member of the Sales department, display a ○ and if not, a ×’.
The COUNTIF function that we included in the IF function states to count if the text in cell B4 begins with ‘Sales’.
In Excel, the ‘~’ part is defined with the ‘* (Asterisk)‘ and in this case, no matter what word follows, it will count all words/phrases including ‘Sales’.
The ‘? (Question mark)‘ allows us to define 1 anonymous letter, so for this case we can write ‘Sales, division ?’.
【Wildcard character symbols】
|*||Allows more than one anonymous character|
|?||Allows one anonymous character|
Codes of text and symbols/CHAR function
When typing formulas and texts, there might be times when we ‘want to set a condition to start a new line with a formula…’ or ‘want to include a symbol we do not know the name for…’.
The CHAR function might be useful in these occasions.
The CHAR function can change text codes in Excel (※All words each have a number assigned) to a text that supports ASCII. Furthermore, by using the CHAR function we can start a new line wherever we wish when merging texts.
[number] = Select ASCII number wished to be displayed.
|Definition of function||Display the text or symbol according to the selected code.|
|EXCEL version||Excel2003・Excel2007 ・ Excel2010 ・ Excel2013 ・ Excel2016|
Example of use 1) Display text using the code
Below are examples of decoding. There are a lot more codes other than below, so it might be entertaining to look at other codes.
① Decode the values in column B
[number] ： Select cell with code, B4.
This might not be a function we use often, but for example, when we combine 2 or more texts together, it can ‘start a new line within the cell’ for us.
Example of use 2) Start a new line within the cell
※When using this operation, we must complete steps (1)~(2) beforehand.
(1) Right click on cell to start a new line and select ‘Format Cells’.
(2) When the dialog box appears, do the following:
① Click ‘Alignment’.
② Check ‘Wrap text’.
③ Click [OK] to complete.
(3) Use the code to start a new line when combining.
The table below shows employee information of a company. In column E, the ‘Zip code’ and the ‘Address’ are combined with a formula using the symbol ‘&’.
Let us see how we can use the CHAR function here to start a new line after the Zip code.
① Combine column C and D together, and start a new line after the information in column C.
[number] ： Select ’10’ that is the code for starting a new line and add it in the original formula “C4&D4“.
=C4& CHAR(10) &D4
Compare dates (Date form and text form)
To compare cells in date format, we can use the DATE function within the formula with the IF function. →For more information on the DATE function, click here
Example 1) Compare cell in date format
The table below is a membership list of a DVD rental shop. The card type has been changed from April 1 2015 so they have decided to notify members that became members before that date.
① Use the DATE function to display ‘Notify’ when date is before column C (membership date) and if not, ‘OK’.
(1) Select ‘April 1 2015’ in the DATE function.
(2) Compare with cell C4 (in date form) using the IF function.
Example of use 2) Compare cell with date in text format
Column C in the table below is not in date form but in text form (Excel does not identify it as a date).
In this case, use the TEXT function and DATEVALUE function to first convert into date. →For more information on TEXT/DATEVALUE function, click here
① Convert dates from text form to date form, and display ‘Notify’ when date is before column C and if not, ‘OK’.
(1) Change the display of cell C4 using the TEXT function.
(2) Convert the date from text to date form using the DATEVALUE function.
DATEVALUE( TEXT(C4,”0000!/00!/00″) )
(3) Compare with date selected in the DATE function (April 1 2015) using the IF function.
=IF( DATEVALUE(TEXT(C4,”0000!/00!/00″)) <DATE(2015,4,1),”Notify”,”OK”)