Logo
×

Excel Nested IF’s Function

Excel Nested IF’s Function

This function in excel helps in checking multiple conditions together by using IF conditions within the IF condition.

Syntax

if(Logical_Test, [value_if_true], if(Logical_Test,
[value_if_true]

, [value_if_false]))

Parameter

Logical_Test (required)- This parameter represents the condition which you want to check (greater than, less than or equal to signs can be used for numbers). When you want to evaluate a condition based on text value, then use double quotes ('') with equal to signs.

value_if_true (optional)- This parameter represents the logical test value, and if it holds the value, then excel will return this value.

value_if_false (optional)- If the logical test does not hold, then excel will return this value.

Nested IF – It represents the various if conditions. From Excel 2007 onwards, we can use 64 IF conditions in one formula.

Category

Logical

Return

The function returns one value if the condition is TRUE and another value if it's FALSE.

Example 1

Objective: Return result of students based on the Criterion given on the right side

Criteria Status
0 – 99 Fail
100 – 150 Pass
150+ Excellent
Name Final Score Result Formula Text
Thomas E. Edison 143 Pass =IF(C178>150,"Excellent",IF(C178<99,"Fail","Pass"))
Shoaib Aktar 91 Fail =IF(C179>150,"Excellent",IF(C179<99,"Fail","Pass"))
Gerard Das 107 Pass =IF(C180>150,"Excellent",IF(C180<99,"Fail","Pass"))
Itrat Sheen 150 Pass =IF(C181>150,"Excellent",IF(C181<99,"Fail","Pass"))
Jack gill 151 Excellent =IF(C182>150,"Excellent",IF(C182<99,"Fail","Pass"))
Willian Stone 139 Pass =IF(C183>150,"Excellent",IF(C183<99,"Fail","Pass"))
Joe Whiteson 178 Excellent =IF(C184>150,"Excellent",IF(C184<99,"Fail","Pass"))
Ronald J. Carlson 120 Pass =IF(C185>150,"Excellent",IF(C185<99,"Fail","Pass"))
Anie Jack 79 Fail =IF(C186>150,"Excellent",IF(C186<99,"Fail","Pass"))
Elmer C. Laslo 128 Pass =IF(C187>150,"Excellent",IF(C187<99,"Fail","Pass"))

Example 2

Objective: Use Nested IF to put the grade a student gets in an exam. Score Ranges for different grades are given in criteria.

Criteria Grade
> 75 A
41-75 B
< 40 C
Student ID Marks Grade Formula
S001 90 A  =IF(E26< 41,"C",IF(E26< 75,"B","A"))
S002 70 B  =IF(E27< 41,"C",IF(E27< 75,"B","A"))
S003 20 C  =IF(E28< 41,"C",IF(E28< 75,"B","A"))
S004 41 B  =IF(E29< 41,"C",IF(E29< 75,"B","A"))

Related Topics

What is a name box in Excel?

The name box in Microsoft Excel specifies a name for a single cell or a table. The name box is present in the top left corner, left to the formula...

4 minutes read.

Excel COUNTA() Function

Excel COUNTA() Function The COUNTA() function counts the number of cells in a range that are not empty. Syntax COUNTA([value1], [value [2], ...) Parameter value1(required)- This parameter represents the first cell in the range. value2, …(optional)- It represents...

1 minute read.

Calculating the Last Day of the Month in Excel

In Microsoft Excel, various functions exist to calculate the date and time from the current date to the past and future. Among multiple tasks in this tutorial, let's see how...

3 minutes read.

Excel Tutorial for Beginners

What is Excel? Microsoft Excel is a software program included in the Microsoft Office suite. It is a commercial spreadsheet application developed by Microsoft for Windows, macOS, Android, and iOS. It...

5 minutes read.

Excel HLOOKUP() Function

Excel HLOOKUP() Function The HLOOKUP() function in excel is used to search a value in the topmost row of a table and then return a corresponding value in the same column for the specified...

2 minutes read.

How to remove duplicate values from excel?

Remove duplicate values from excel Duplicate values occur when the same date or set of values is repeated in your Excel sheet. While working with Excel sheets many a time, the...

5 minutes read.

How to generate random numbers in Excel

In this tutorial, we will discuss the following things in detail that are as follows: Introduction about Random Numbers used in Microsoft Excel. Discuss how to generate the Random Numbers in Microsoft...

6 minutes read.

Separate Strings in Excel

How to Separate String in Excel Day by day, Microsoft Excel is increasing for business and personal use. It is a combination of numbers and alphabets based on the data provided....

4 minutes read.

Pivots Table in Excel

What is a Pivot? A pivot table is a tool to create summary reports from data-sets irrespective of their sizes but is helpful even if the data is more than just...

5 minutes read.

Square Root Function in Excel

The concept of Mathematical calculations includes a large number of numbers and calculations are based on the numbers present in the data. Likewise, Square Root is a function used to...

3 minutes read.

How to lock cells in Excel?

How to lock cells in Excel? The locked cells feature used to protect and secure your Excel sheet or workbook from the unauthorized access. If the cells are locked, it can’t be deleted, reformatted,...

5 minutes read.

Go-To Special function in Excel

Go-to Special The Go-To Special function in Excel allows you to select all cells that meet certain criteria quickly. This command is used to identify specific cells – the ones which...

1 minute read.

Solver in Excel

What is Solver? Microsoft Excel facilitates an add-in programming tool known as Solver that uses operational research techniques to find the optimal solutions for objective problems. This tool operates with a group of...

5 minutes read.

Excel SUBSTITUTE() Function

The SUBSTITUTE() function in excel substitutes/replaces the old text with the new text in a string. SUBSTITUTE function is used to clean data. This function is case-sensitive. Syntax SUBSTITUTE(Text, Old_Text, New_Text, [instance_number]) Parameter Text (required)- This...

2 minutes read.

Excel COUNTBLANK() Function

Excel COUNTBLANK() Function The COUNTBLANK() function in excel counts the number of blank cells within a range. Syntax COUNTBLANK ([value1], [value [2], ...) Parameter value1(required)- This parameter represents the first cell in the range. value2, …(optional)- It represents...

1 minute read.

Excel WEEKDAY() Function

The WEEKDAY () function in excel returns number from 1 (Sunday) to 7 (Saturday) identifying the day of the week based on its date. This function Default return is - Sunday would be...

1 minute read.

Excel MOD() Function

Excel MOD() Function The MOD() function in excel returns the remainder after a number is divided by a divisor Syntax MOD(number, divisor) Parameter Number(required)- This parameter represents the number which you want to divide. Divisor(required)- This parameter represents...

1 minute read.

How to Alphabetize in Excel?

Alphabetize in Excel One of the reasons for Excel's widest popularity is its ability to swiftly and effortlessly sort data. Excel facilitates easy methods that alphabetically sort the lists of strings...

5 minutes read.

Data Validation in Excel

What is Data Validation? Data Validation is one of the features in Excel that allows user to restrict values which other people can fill in – for example, in a form, you may...

4 minutes read.

Shortcut Keys in Excel

Shortcut Keys in Excel Shortcut keys in Excel are used to simplify the work and save time by pressing a few keys.  These keys are used for navigating and working with Excel spreadsheets quickly and easily. Basic Selection:...

9 minutes read.