If Formula in excel by@yogendra5575

#IF Statements-

Formula: =IF(logical_statement, return this if logical statement is true, return this if logical statement is false)

When you’re doing an analysis of a lot of data in Excel there are a lot of scenarios you could be trying to discover and the data has to react differently based on a different situation.

Continuing with the sales example: Let’s say a salesperson has a quota to meet. You used VLOOKUP to put the revenue next to the name. Now you can use an IF statement that says: “IF the salesperson met their quota, say “Met quota”, if not say “Did not meet quota” (Tip: saying it in a statement like this can make it a lot easier to create the formula, especially when you get to more complicated things like Nested IF Statements in Excel).

It would look like this:

In the example with the VLOOKUP we had the revenue in column B and the person’s name in column C (brought in with the VLOOKUP). We could put their quota in column D and then we’d put the following formula in cell E1:

=IF(C3>D3, “Met Quota”, “Did Not Meet Quota”)

This IF statement will tell us if the first salesperson met their quota or not. We would then copy and paste this formula along all the entries in the list. It would change for each sales person.

Having the result right there from the IF statement is a lot easier than manually figuring this out.

Excel IF Function

Summary 

The IF function can perform a logical test and return one value for a TRUE result, and another for a FALSE result. For example, to "pass" scores above 70: =IF(A1>70,"Pass","Fail"). More than one condition can be tested by nesting IF functions. The IF function can be combined with logical functions like AND and OR.

Purpose 

Test for a specific condition

Return value 

The values you supply for TRUE or FALSE

Syntax 

=IF (logical_test, [value_if_true], [value_if_false])

Arguments 

• logical_test - A value or logical expression that can be evaluated as TRUE or FALSE.
• value_if_true - [optional] The value to return when logical_test evaluates to TRUE.
• value_if_false - [optional] The value to return when logical_test evaluates to FALSE.

Usage notes 

Use the IF function to test for or evaluate certain conditions, and then react differently depending on whether the test was TRUE or FALSE.

In the example shown, we want to assign either "Pass" or "Fail" based on a test score. A passing score is 70 or higher. The formula in D6, copied down, is:

=IF(C6>=70,"Pass","Fail")

Translation: If the value in C6 is greater than or equal to 70, return "Pass". Otherwise, return "Fail".

The logical flow this formula could be reversed. A formula with the same result could be written like this:

=IF(C6<70,"Fail","Pass")

Translation: If the value in C6 is less than 70, return "Fail". Otherwise, return "Pass".

Either formula above, when copied down the column, will test every score and return the correct result.

Note: If you are new to the idea of formula criteria, this article shows many examples.

Nested IF statements

The IF function can be "nested". A "nested IF" refers to a formula where at least one IF function is nested inside another in order to test for more conditions and return more possible results. Each IF statement needs to be carefully "nested" inside another so that the logic is correct.

For example, the following formula can be used to assign an grade rather than a pass / fail result:

=IF(C6<70,"F",IF(C6<75,"D",IF(C6<85,"C",IF(C6<95,"B","A"))))

Up to 64 IF functions can be nested. However, in general, you should consider other functions, like VLOOKUP or HLOOKUP for more complex scenarios, because they can handle more conditions in much more streamlined fashion.

Logical operators

When you are constructing a test with IF, you can use any of the following logical operators: