8.2 NESTING AND, OR, NOT FUNCTIONS INSIDE AN IF FUNCTION – Excel For Decision Making (2024)

Table of Contents

Attributions: Media Attributions FAQs References

Let us suppose you are in the process of deciding which university to attend for your Master Degree Program. You have a list of criteria that must be met, or you will not choose that school. You may have a list similar to the one below:

The university you want to attend:

Must have a Graduate program.
Must have a Business program.
Must not put you into a debt greater than $20,000.
Must lead to earnings of at least $60,000 per year after 10 years.

In our data dictionary, we can locate fields in our data set that will provide us with useful information.

Graduate program related field and condition: [@HIGHDEG]=4
Business program related field and condition: [@PCIP52]>0
Debt related field and condition: [@[GRAD_DEBT_MDN_SUPP]]<20000
Earnings-related field and condition: [@[MD_EARN_WNE_P10]]>60000

If you think about it, these conditions boil down to a few simple logical tests, like ones covered in Chapter 3 when we used the IF function to see if someone has passed or failed a test. Here, we have 4 logical tests that ALL must be TRUE before you can pick from a list of institutions that all meet your criteria. In our College Scorecard dataset and its related Data Dictionary, we have fields that we can use to filter an Excel table or a Pivot Table to narrow down our choices. However, in this chapter, we will look at using logical functions to find an answer.

IF you were fond of using logical functions, THEN you may find the use of the following functions in combination with our IF function. On their own, the AND, OR, NOT are logical functions that will help you evaluate up to 255 conditions and return a TRUE or FALSE value. The AND logical function determines if ALL conditions in a test are TRUE. The OR logical function determines if ANY conditions in a test are TRUE. The NOT logical function makes sure one value is not equal to another.

The syntax for these three functions are as follows:

=AND(logical1,[logical2], …)

=OR(logical1,[logical2], …)

Attributions:

Chapter 8 by Emese Felvégi and Robert McCarn. CC BY-NC-SA 3.0.

Media Attributions

AND
ANDv2

FAQs

8.2 NESTING AND, OR, NOT FUNCTIONS INSIDE AN IF FUNCTION – Excel For Decision Making? ›

nesting and, or, not inside an if function. Using AND, OR, NOT in conjunction with the IF function will let you return custom outputs or run calculations. You can automate the evaluation of logical tests by NESTING the AND, OR, NOT functions inside a single IF function.

How do you use if and nested IF function in Excel? ›

If you clicked IF, the Function arguments dialog box displays the arguments for the IF function. To nest another function, you can enter it into the argument box. For example, you could enter SUM(G2:G5) in the Value_if_true box of the IF function. Enter any additional arguments that are needed to complete your formula.

View Details ›

Can you put a function inside an if statement Excel? ›

Nested IF functions, meaning one IF function inside of another, allow you to test multiple criteria and increases the number of possible outcomes.

View Details ›

Which function is used for decision making in Excel? ›

Use an IF Function to make logical comparisons between a value and what you expect. Create a VLOOKUP calculation to look up information in a table.

Know More ›

Can you nest the and or or functions within an if function? ›

You can automate the evaluation of logical tests by NESTING the AND, OR, NOT functions inside a single IF function. This means that if we have multiple conditions but we want to return a single output, we can nest any of the conjunction functions inside an IF and specify outputs accordingly.

What is an example of a nested if statement? ›

A nested if statement is an if-else statement with another if statement as the if body or the else body. Here's an example: if ( num > 0 ) // Outer if if ( num < 10 ) // Inner if System. out.

Get More Info ›

Can a function be inside an if statement? ›

But to answer your question you can definitely put an if statement in a function. A function is just a block of code that sets a side a chunk of code for easy access when called. So you can pretty much put anything in a function.

Read On ›

Can you put a formula in an if function? ›

Simple IF examples

In this example, the formula in cell D2 says: IF(C2 = 1, then return Yes, otherwise return No)As you see, the IF function can be used to evaluate both text and values. It can also be used to evaluate errors.

Which is the decision-making function? ›

A decision-making process is a series of steps one or more individuals take to determine the best option or course of action to address a specific problem or situation. Often, managers and executives use the process to plan how to carry out business initiatives or set specific actions in motion.

Know More ›

How do you use an IF and match function in Excel? ›

To compare two ranges cell-by-cell and return the logical value TRUE if all the cells in the corresponding positions match, supply the equally sized ranges to the logical test of the AND function: AND(range A = range B) That's how to use the If match formula in Excel.

Read The Full Story ›

How do you use if and else function in Excel? ›

Use =IF(logical_test, [value_if_true], [value_if_false]) to create an if-else statement. Add AND, OR, or NOT functions to evaluate more complex situations. Create nested IF statements to evaluate multiple conditions at different levels.