facebook twitter pinterest google plus Youtube
www.TestsTestsTests.com

www.TestsTestsTests.com

Nested Functions in Excel Tutorial – Free online Microsoft Excel 2010 Tutorial – Formulas and Functions in Excel

Nested Functions in Excel Tutorial

Free Online Microsoft Excel 2010 Tutorial
Formulas and Functions in Excel

* Using a Nested IF function Excel
* Using the OR function in Excel
* Using the AND function in Excel
* Using the NOT function
in Excel

Nested functions add super powers to your normal formulas and functions and allow you to perform more complex calculations. Nested functions can contain OR, AND, NOT and multiple IF arguments.

Test your Excel skills with the corresponding FREE Online Multiple Choice
Excel Using Nested Functions Test



* Using a Nested IF function Excel

A nested IF is simply one or more IF function within the same formula.  Using a nested IF allows you to evaluate more than one condition without having to create multiple formulas to do so.


Study the screenshot of a worksheet below:


Using a Nested IF function - Image 1 - Excel Tutorial


To calculate the Bonus for each dealer in column H based on:

  • * If the total sales for a dealer is higher than $100,000, the dealer earns a $1000 bonus;
  • * If the total sales for a dealer is higher than $50,000, the dealer earns a $500 bonus;
  • * A dealer can only earn a bonus if they have completed training (as noted with a Yes or No in the Completed Training column)

The syntax for this formula when entered into cell H5 would be:

=IF(I5=”No”,”-“, IF(F5>100000, 1000, IF(F5>50000,500,”-“)))


What this formula evaluates:

  • * If cell I5 contains the value No, then insert a hyphen in H5 and stop; else
  • * If cell I5 contains the value Yes, then proceed to evaluate the value of cell F5;
  • * If cell F5 contains a value greater than 100,000 then insert a value of 1,000 in cell H5; else
  • * If cell F5 contains a value greater than 50,000 then insert a value of 500 in cell H5, else
  • * If cell F5 contains a value that is less than 50,000 insert a hyphen in cell H5.

 

Compare the above breakdown to the arguments listed for calculating the Bonus column amount earlier in this tutorial.

You can nest an IF in the value_if_false or a value_if_true part of another IF function.  Depending on the specific calculation needed, you can nest multiple IF functions in one formula. 

The best way to master nesting IF functions, is to recreate the scenario above for yourself and to experiment  with different conditions and calculations.

Compare your results to the completed Bonus column in the screenshot below:

Using a Nested IF function - Image 2 - Excel Tutorial



* Using the OR function in Excel

You can use the OR function by itself to evaluate whether certain conditions are TRUE or FALSE or within an IF function to evaluate multiple conditions.  The OR function checks whether a condition is met and evaluates to TRUE if one or all conditions are met and FALSE if none are met.

The syntax for the OR function is as follows:

=OR(Logical_1, [Logical_2],…)


For example:


Using the OR function - Image 1 - Excel Tutorial


In the screenshot above, the OR function evaluates whether cell A1 contains the word Apples OR cell A2 contains the word Oranges If we changed one of the words in column A to, for example, Bananas, the OR function would still evaluate to TRUE.  If we changed both words in column A to, for example, Bananas and Lemons, the OR function will evaluate to FALSE as neither of the logical conditions set have been met.

It is far more common, and probably useful, to use an OR argument within an IF function.  The syntax for using OR within an IF function is as follows:

=IF(OR(Logical_1, Logical_2), value_if_true, value _if_false)


Using a simple example to demonstrate this:

Using the OR function - Image 2 - Excel Tutorial



In the above screenshot example, instead of using just the OR function to return a TRUE or FALSE result for cell B1, it is nested within an IF function where the value_if_true and value_if_false outcomes can be set.  In this case, the formula checks whether cell A1 has the word Apples OR cell A2 has the word Oranges.  If either of these conditions evaluated to TRUE, cell B1 would display the word Yes.  If neither condition evaluates to TRUE, cell B1 should display the phrase Not Applicable.


* Using the AND function in Excel

You can use the AND function by itself to evaluate whether certain conditions are TRUE or FALSE or within an IF function to evaluate multiple conditions.  The AND function checks whether conditions are met and only evaluates to TRUE if ALL logical conditions in the formula are met.  It will evaluate to FALSE if even just one condition is not met.

The syntax for the AND function is as follows:

=AND(Logical_1, [Logical_2],…)


For example:

Using the AND function - Image 1 - Excel Tutorial



In the screenshot above, the AND function evaluates whether A1 contains the word Apples AND A2 contains the word Oranges If we change one of the words in column A to, for example, Passion Fruit, the AND function will evaluate to FALSE.  All arguments in an AND functions must be met for it to evaluate to TRUE.

It is far more common, and probably useful, to use an AND argument within an IF function.  The syntax for using AND within an IF function is as follows:

=IF(AND(Logical_1, Logical_2), value_if_true, value_if_false)


Using a simple example to demonstrate this:

Using the AND function - Image 2 - Excel Tutorial



In the above screenshot example, instead of using just the AND function to return a TRUE or FALSE result for cell B1, it is nested within an IF function where the value_if_true and value_if_false outcomes can be set.  In this case, the formula checks whether cell A1 has the word Apples AND cell A2 has the word Oranges.  If both of these conditions evaluated to TRUE, cell B1 would display the word Yes.  If one of these conditions evaluates to FALSE, cell B1 should display the phrase Not Applicable (as evident in the screenshot example above).



* Using the NOT function in Excel

The NOT function is an interesting one to get your head around.  The NOT function reverses the value of its argument, for example, you state the result you want if the condition specified is NOT TRUE

The syntax for the NOT function is as follows:

=NOT(Logical)


For example:


Using the NOT function - Image 1 - Excel Tutorial


In the screenshot example above, the NOT function evaluates whether cell A1 contains the word Grapes and as it is TRUE, it reverses the value of the argument in cell B1 to FALSE.  If we changed the word in cell A1 to Apples, for example, B1 will evaluate to TRUE

As with the OR and AND functions, It is far more common and probably useful to use the NOT argument within an IF function.  The syntax for using NOT within an IF function is as follows:

=IF(NOT(logical), value_if_true, value_if_false)


Using a simple example to demonstrate this:


Using the NOT function - Image 2 - Excel Tutorial



The example in the screenshot above evaluates whether cell A1 DOES NOT contain the word Grapes and will return the value Yes if it doesn’t and the phrase Not Applicable if it does.

The best way to master nested IF, OR, AND and NOT, is to start by creating simple scenarios such as those demonstrated in this tutorial and then work your way up to using these functions for ever more complex calculations.



Woohoo! Now that you have done the tutorial:


Test your Excel skills with the corresponding FREE Online Multiple Choice
Excel Using Nested Functions Test


TRY THE NEXT TUTORIAL: Financial Functions in Excel Tutorial

TRY THE NEXT TEST: Financial Functions in Excel Test




* More from TestsTestsTests.com