Anzeige

Apply nested functions in Excel

Most users are familiar with the simple functions in Excel. However, only a few know that functions in Excel can also be nested with one another. By nesting functions, Excel offers exactly the possibilities that cannot be mapped by the individual functions.

Nested functions in Excel can be mapped on up to 64 levels, and thus also enable very complex function models and application scenarios to be displayed. In our tutorial, we would like to take a quick look at how nested functions work in Excel.

Verschachtelte Funktionen in Excel anwenden

Topic Overview

Anzeige

Apply nested functions in Excel

Most users are familiar with the simple functions in Excel. However, only a few know that functions in Excel can also be nested with one another. By nesting functions, Excel offers exactly the possibilities that cannot be mapped by the individual functions.

Nested functions in Excel can be mapped on up to 64 levels, and thus also enable very complex function models and application scenarios to be displayed. In our tutorial, we would like to take a quick look at how nested functions work in Excel.

Verschachtelte Funktionen in Excel anwenden

Topic Overview

Anzeige

Nesting and combining functions in Excel

In order to show the nesting and combining of functions in Excel as transparently as possible, we have created an example table. Customers should receive a predefined discount under various conditions. To do this, we nested an IF function and an OR function, and then again with an IF function and an AND function.

This allows different discounts to be displayed depending on the turnover made and an additional combination with the order quantity.

In the first example, we’ll look at combining an IF function with an AND function. The following conditions must be met for the customer to receive a discount:

The customer should receive a discount of 5% on the retail price if the order quantity is greater than 5 pieces. And this regardless of the turnover.
The customer should receive a discount of 10% on the retail price if the order quantity is greater than 5 pieces and the turnover is over €1,000.
If none of the conditions are met, the cell should say No discount.

see fig. (click to enlarge)

WENN-UND-Funktion in Excel
Advertisement

The full nested function in Excel looks like this to represent the desired discount or “No discount” notice:

=IF(E6>10;0,05;IF(AND(E6>5;F6>1000);0,1;”No discount”))

We will now take the function apart piece by piece for the sake of explanation

  • =IF( = Here we open the IF function
  • =IF(E6>10;0,05; = The first part of the function says that IF the order quantity is greater than 10 pieces THEN the order quantity is greater than 10 pieces THEN the cell there should be filled with the value 0.05. Due to the formatting of the cell as a percentage value there later 5% displayed correctly.
  • IF(AND( = Instead of supplying the value for ELSE as usual with an IF function, we immediately insert the next IF function and combine it with an AND function.
  • E6>5;F6>1000) = With the AND-function we specify additional arguments that must be fulfilled in order to receive the 10% discount. In this part of the function we say that IF the order quantity is greater than 5 pieces, AND the purchase Value is over 1.000€ than… From here we close the AND function again with the brackets.
  • ;0,1;”No discount”)) = In the last part of the function, we continue the IF function we started at the beginning, and state the steps that Excel should take if the two arguments that must be fulfilled for the 10% are true . So if the quantity is more than 5 pieces and the sales price is more than €1,000, the cell should be filled with the value 0.1, and if the two arguments do not apply together, “No discount” should appear there. With the two parentheses at the end, we close our nested function.

Here again the fully nested IF AND function with which both 5%, 10% and also the note “No discount” can be displayed.
=IF(E6>10;0,05;IF(AND(E6>5;F6>1000);0,1;”No discount”))

By the way:
You can automate the colored markings in the column with the percentage values and the note “No discount” with conditional formatting in Excel. Click here for the article

Of course, you can also combine the whole thing with an IF-OR function, in which, for example, not several conditions, but only one of the two (or more) must be fulfilled in order to receive a corresponding discount, or even the message “No discount”.

The nested function in Excel would then look like this:

=IF(N5>10;0,05;IF(OR(N5>5;O5>1000);0,1;”No discount”))

siehe Abb. (klicken zum vergrößern)

WENN-ODER-Funktion in Excel
Advertisement

You can download the Excel file that we have built to illustrate the facts clearly here for free to experiment with it.

Download exercise file
Blogverzeichnis Bloggerei.de

Nesting and combining functions in Excel

In order to show the nesting and combining of functions in Excel as transparently as possible, we have created an example table. Customers should receive a predefined discount under various conditions. To do this, we nested an IF function and an OR function, and then again with an IF function and an AND function.

This allows different discounts to be displayed depending on the turnover made and an additional combination with the order quantity.

In the first example, we’ll look at combining an IF function with an AND function. The following conditions must be met for the customer to receive a discount:

The customer should receive a discount of 5% on the retail price if the order quantity is greater than 5 pieces. And this regardless of the turnover.
The customer should receive a discount of 10% on the retail price if the order quantity is greater than 5 pieces and the turnover is over €1,000.
If none of the conditions are met, the cell should say No discount.

see fig. (click to enlarge)

WENN-UND-Funktion in Excel
Advertisement

The full nested function in Excel looks like this to represent the desired discount or “No discount” notice:

=IF(E6>10;0,05;IF(AND(E6>5;F6>1000);0,1;”No discount”))

We will now take the function apart piece by piece for the sake of explanation

  • =IF( = Here we open the IF function
  • =IF(E6>10;0,05; = The first part of the function says that IF the order quantity is greater than 10 pieces THEN the order quantity is greater than 10 pieces THEN the cell there should be filled with the value 0.05. Due to the formatting of the cell as a percentage value there later 5% displayed correctly.
  • IF(AND( = Instead of supplying the value for ELSE as usual with an IF function, we immediately insert the next IF function and combine it with an AND function.
  • E6>5;F6>1000) = With the AND-function we specify additional arguments that must be fulfilled in order to receive the 10% discount. In this part of the function we say that IF the order quantity is greater than 5 pieces, AND the purchase Value is over 1.000€ than… From here we close the AND function again with the brackets.
  • ;0,1;”No discount”)) = In the last part of the function, we continue the IF function we started at the beginning, and state the steps that Excel should take if the two arguments that must be fulfilled for the 10% are true . So if the quantity is more than 5 pieces and the sales price is more than €1,000, the cell should be filled with the value 0.1, and if the two arguments do not apply together, “No discount” should appear there. With the two parentheses at the end, we close our nested function.

Here again the fully nested IF AND function with which both 5%, 10% and also the note “No discount” can be displayed.
=IF(E6>10;0,05;IF(AND(E6>5;F6>1000);0,1;”No discount”))

By the way:
You can automate the colored markings in the column with the percentage values and the note “No discount” with conditional formatting in Excel. Click here for the article

Of course, you can also combine the whole thing with an IF-OR function, in which, for example, not several conditions, but only one of the two (or more) must be fulfilled in order to receive a corresponding discount, or even the message “No discount”.

The nested function in Excel would then look like this:

=IF(N5>10;0,05;IF(OR(N5>5;O5>1000);0,1;”No discount”))

siehe Abb. (klicken zum vergrößern)

WENN-ODER-Funktion in Excel
Advertisement

You can download the Excel file that we have built to illustrate the facts clearly here for free to experiment with it.

Download exercise file
Blogverzeichnis Bloggerei.de

Search for other topics:

About the Author:

Michael W. Suhr
Michael W. SuhrDipl. Betriebswirt | Webdesign- und Beratung | Office Training
After 20 years in logistics, I turned my hobby, which has accompanied me since the mid-1980s, into a profession, and have been working as a freelancer in web design, web consulting and Microsoft Office since the beginning of 2015. On the side, I write articles for more digital competence in my blog as far as time allows.

Search by category:

You might also be interested in:

Anzeige

Search for other topics:

About the Author:

Michael W. Suhr
Michael W. SuhrDipl. Betriebswirt | Webdesign- und Beratung | Office Training
After 20 years in logistics, I turned my hobby, which has accompanied me since the mid-1980s, into a profession, and have been working as a freelancer in web design, web consulting and Microsoft Office since the beginning of 2015. On the side, I write articles for more digital competence in my blog as far as time allows.

Search by category:

You might also be interested in:

Anzeige

Popular Posts:

1907, 2022

Encrypt emails in Outlook – How it’s done

July 19th, 2022|Categories: Data Protection, Homeoffice, Microsoft Outlook, Office 365, Uncategorized|Tags: , , , , , , |

Standard e-mails are like postcards that everyone can theoretically read. But there are solutions for more security.

1707, 2022

Environmentally friendly printing for more climate protection

July 17th, 2022|Categories: Homeoffice, Microsoft Windows, Uncategorized|Tags: , |

Climate protection starts small. And so not only can costs be saved through environmentally friendly and conscious printing in the office, but the climate can also be protected.

Special Offers: Word & Excel Templates

More

Special Offers: Design CV Templates

See more

Monatliche Technik Bestseller:

Bestseller 2022-2023 WLAN-Heizkoerperthermostate

Kategorie: SmartHome | Energie & Sicherheit

SmartHome | Energie & Sicherheit

Bestseller 2022-2023 WLAN-Heizkoerperthermostate
Produkte anzeigen
Bestseller 2022-2023 Notebooks

Kategorie: Homeoffice

Homeoffice

Bestseller 2022-2023 Notebooks
Produkte anzeigen
Bestseller 2022-2023 Smartphones

Kategorie: Smartphone & Zubehör

Smartphone & Zubehör

Bestseller 2022-2023 Smartphones
Produkte anzeigen

Popular Posts:

1907, 2022

Encrypt emails in Outlook – How it’s done

July 19th, 2022|Categories: Data Protection, Homeoffice, Microsoft Outlook, Office 365, Uncategorized|Tags: , , , , , , |

Standard e-mails are like postcards that everyone can theoretically read. But there are solutions for more security.

1707, 2022

Environmentally friendly printing for more climate protection

July 17th, 2022|Categories: Homeoffice, Microsoft Windows, Uncategorized|Tags: , |

Climate protection starts small. And so not only can costs be saved through environmentally friendly and conscious printing in the office, but the climate can also be protected.

Special Offers: Word & Excel Templates

More

Special Offers: Design CV Templates

See more

Monatliche Technik Bestseller:

Bestseller 2022-2023 WLAN-Heizkoerperthermostate

Kategorie: SmartHome | Energie & Sicherheit

SmartHome | Energie & Sicherheit

Bestseller 2022-2023 WLAN-Heizkoerperthermostate
Produkte anzeigen
Bestseller 2022-2023 Notebooks

Kategorie: Homeoffice

Homeoffice

Bestseller 2022-2023 Notebooks
Produkte anzeigen
Bestseller 2022-2023 Smartphones

Kategorie: Smartphone & Zubehör

Smartphone & Zubehör

Bestseller 2022-2023 Smartphones
Produkte anzeigen
2022-08-04T12:09:29+02:00By |Categories: Microsoft Excel, Microsoft Office, Office 365|Tags: |

Title

Anzeige

Most read:

Search by category:

Alle Vorlagen ansehen

Auch interessant:

Go to Top