Tips

The IF command in Excel and its practical applications

Ngoc Phuong

Ngoc Phuong

2021-08-31 . 84 view

The IF command in Excel is one of the basic functions used for calculations and statistics. The IF command can be combined with logic like Or, And, Not to produce statistical results for multiple conditions at once. Spend just 30 seconds to learn a detailed guide on how to use the IF function. Mastering the IF command helps you calculate quickly and most accurately in Excel.

I. The standard formula of the IF command in Excel

The standard syntax of the IF command in Excel:

=/+IF(logical_test, value_if_true, value_if_false)

Where:

  • =/+: When using functions in Excel calculations, it is mandatory to place an "=" or "+" sign before the function formula. 
  • IF: function name
  • logical_test: conditional expression
  • value_if_true: value returned if the condition is met
  • value_if_false: value returned if the condition is not met.

II. Notes on using the IF command in Excel

When writing the IF command, there are a few minor issues to note.

  • The IF command can be written in uppercase or lowercase, such as IF, if, If.
  • If value_if_true is left blank, meaning the function formula is =/+IF(logical_test,, value_if_false), then the value returned if the condition is true will be 0 or “-”.
  • If value_if_false is left blank, meaning the function formula is =/+IF(logical_test, value_if_true,), then the value returned if the condition is false will be 0 or “-”.
  • value_if_true and value_if_false can be in text or numeric form. If in text form, it must be enclosed in quotes “ “.

III. Application examples of the IF command in Excel

The IF command in Excel can be used for independent calculations. If a calculation requires many specific conditions, you can combine the If function with other logical functions like Or, And, Not, nested If functions.

1. Simple IF command example

For instance, classifying as “Pass” or “Fail”. A total score of 20 or above is a pass, otherwise, it is a fail.

In cell G2, use the IF command to classify students. Pass when the total score is 20 or above, otherwise, it is Fail.

Copy cell G2 and paste it into the other cells to get the classification of all students.

2. Example of nested IF commands

Example of classification as Excellent, Good, Average, Poor. Excellent for scores of 8.5 or higher, Good from 6.5 to below 8.5, Average from 5 to below 6.5, the rest are Poor.

In cell G2, use multiple nested IF commands to classify based on the Average score. With 4 classification cases, you must use 3 IF functions. In this case, because 3 IF functions are used, parentheses are closed three times.

3. IF command and And function

The And function formula is =/+AND(logical1, logical2,...)

The function name can be written in uppercase or lowercase as preferred.

Logical1, logical2,... are the set conditions.

The object must meet all the set conditions to be selected.

Example of final summarization between academic and conduct scores. If the academic score is “Excellent” and the conduct score is “Good”, the final summarization is “Outstanding”. Other cases are classified according to academic performance.

In cell I2, use the And function to combine two conditions on academic and conduct for the Outstanding final grade. Combine the And and If functions to classify as “Outstanding” and other cases.

4. IF command and Or

The Or function formula: =/+OR(logical1, logical2,...)

The function name can be written in uppercase or lowercase as preferred.

Logical1, logical2,... are the set conditions.

The object only needs to meet one of the set conditions to be selected.

Example of classification as “Pass” or “Fail”. If either Math or Literature is below 5 points, it is a fail; otherwise, it is a pass.

In cell I2, use the Or function to check if anyone has one of two subjects below 5 points and use the IF command to classify as “Pass” or “Fail”.

The IF command in Excel has many applications for evaluation and classification. Combining the IF command with other logical functions increases the computing power in Excel. Those learning basic Excel must thoroughly understand the IF command.

Ngoc Phuong
Ngoc Phuong

Web Developer

Thank you for visiting my website. My name is Ngoc Phuong, and I have over 10 years of experience in website development. I am confident in stating that I am an expert in creating impressive and effective websites. If you need a website designed, please feel free to contact me via email at [email protected].

0 feedback