How to Excel Nested IF Formula (Advanced IF)
Excel Made Easy - TheTechGeek
-
================================================================================
Link to Full YouTube Video https://youtu.be/UHQT4pqvV_I
================================================================================
Hi Guys, Welcome to my channel and page TheTechGeek. This video is part of the series on Microsoft Excel.
Today, TheTechGeek is here to discuss what is advanced IF function and how to use nested IF function in different business scenarios. Stay tuned till the end to learn something about nested IF.
Let's get started.
In our previous video, we discussed what is basic IF function. Later we saw three arguments of IF function which are Logical Test, True Expression and False Expression. Lastly, we saw an example where we applied IF function to calculate surcharge on fragile items based on one logical test with one true and false expression. Now let’s see what is advanced IF function.
Use of advanced IF function involves using more than one IF formula in excel formula to enable logical testing on multiple criteria to increase the number of possible outcomes. Nested IF is when you place another IF function in FALSE argument to check other logical criteria.
To get a better idea on this, have a look at the upcoming example.
Example 1 Grades. Assume you are working in a business school as a professor assistant. Professor has given you assignments of students for preparing a summary where you need to list down student marks and assign grades to students according to their marks allocated by the professor. This summary is required urgently for the next class today afternoon in which the professor has to announce the result of assignments submitted by students. Professor has told you to assign ‘A’ grade for students with marks equal to and greater than ninety. Then assign ‘B’ grade for students with marks equal to and greater than eighty but less than ninety. Then assign ‘C’ grade to students with marks equal to and greater than 70 but less than 80. Then assign ‘D’ grade to students with marks equal to and greater than 60 but less than 70 while assigning ‘F’ grade to students with less than 60 marks.
For achieving this purpose, we will write nested IF formula which is equal to I F left parenthesis F3, greater than and equal to, ninety, comma, inverted comma, A, inverted comma, again comma, IF left parenthesis F3, greater than and equal to, eighty, comma, inverted comma, B, inverted comma, comma, IF left parenthesis F3, greater than equal to, seventy, comma, inverted comma, C, inverted comma, again comma, IF left parenthesis F3, greater than equal to, sixty, comma, inverted comma, D, inverted comma, comma, inverted comma, F, inverted comma right parenthesis four times.
Now copy this formula to other cells to assign grades to all students in one go. I would like to explain that you don’t need to write full equation such as greater than and equal to 80 but less than 90 because second IF expression will only be processed if the value being evaluated is less than ninety. If this was not the case, grade A would have assigned in first IF expression. This sequence goes on till F as we didn’t write that assign F to students with marks less than 60 because it is implied with the nested IF that if previous expressions are false, value is definitely less than sixty.
Example 2 Sales Commission. Assume that you have recently joined in the finance department of a company and your company pays commission to sales team against weekly sales made by the sales team. You have been asked to calculate sales commission for the last week sales so that same can be paid to the sales team. You can easily do this task by nested IF formula as we saw in the previous example.
You need to write correct syntax for nested IF according to current situation which is equal to I F left parenthesis F3, greater than and equal to, eight hundred, comma, ten percent, comma, again IF left parenthesis F3, greater than and equal to, seven hundred, comma, eight percent, comma, again IF left parenthesis F3, greater than and equal to, six hundred, six percent, comma, again IF left parenthesis F3, greater than and equal to, five hundred, comma, four percent, comma, zero percent right four parenthesis.
This syntax will calculate sales commission percentage based on which sales commission of employees can be calculated by simply multiplying sales commission percentage with weekly sales.
In this video, we briefly recalled what the basic IF function does and the syntax to use it and what makes it advanced IF or nested IF. Later on, two business scenarios were discussed to see how the advanced IF function can help in a real-life situation. I hope I explained it very well, but still, if you have any query, you can drop your question in the comments section.
If you learned something new from this video, I would be happy to know about this through the comments section. Hope you have liked the content by now. Please also share it with your friends too so that they can also learn something new.
Channel and Page links are available in description. Subscribe my channel on YouTube and click bell icon and also like my page on Facebook and follow. This way you will be able to keep track of my upcoming new videos. Link to related excel videos playlist is appearing on your YouTube screen now. Check these out for learning more.
Thank you for watching the video
================================================================================
In this video, Learn to use Excel IF function within IF function which is called Nested IF, also get to know where it can be used in real life situation. This will be done through a walkthrough of two real-life examples to understand how to use advanced IF function in a business scenario.
Share it with your friends to also let them know how to use the Nested IF function in Microsoft Excel.
Link for IF Function (Basic): https://youtu.be/rmNga7A5980
For more vidoes on tips for making your life easier, please subscribe me on YouTube and turn on notifications by pressing Bell Icon through YouTube Channel: https://www.youtube.com/user/greatbhatti
You can also find me on Facebook.
Facebook Page: https://fb.me/TheTechGeekOriginal
#HowToExcel #LogicalFormula #Tutorial #ExcelWithTheTechGeek
================================================================================
================================================================================
Link to Full YouTube Video https://youtu.be/UHQT4pqvV_I
================================================================================
Hi Guys, Welcome to my channel and page TheTechGeek. This video is part of the series on Microsoft Excel.
Today, TheTechGeek is here to discuss what is advanced IF function and how to use nested IF function in different business scenarios. Stay tuned till the end to learn something about nested IF.
Let's get started.
In our previous video, we discussed what is basic IF function. Later we saw three arguments of IF function which are Logical Test, True Expression and False Expression. Lastly, we saw an example where we applied IF function to calculate surcharge on fragile items based on one logical test with one true and false expression. Now let’s see what is advanced IF function.
Use of advanced IF function involves using more than one IF formula in excel formula to enable logical testing on multiple criteria to increase the number of possible outcomes. Nested IF is when you place another IF function in FALSE argument to check other logical criteria.
To get a better idea on this, have a look at the upcoming example.
Example 1 Grades. Assume you are working in a business school as a professor assistant. Professor has given you assignments of students for preparing a summary where you need to list down student marks and assign grades to students according to their marks allocated by the professor. This summary is required urgently for the next class today afternoon in which the professor has to announce the result of assignments submitted by students. Professor has told you to assign ‘A’ grade for students with marks equal to and greater than ninety. Then assign ‘B’ grade for students with marks equal to and greater than eighty but less than ninety. Then assign ‘C’ grade to students with marks equal to and greater than 70 but less than 80. Then assign ‘D’ grade to students with marks equal to and greater than 60 but less than 70 while assigning ‘F’ grade to students with less than 60 marks.
For achieving this purpose, we will write nested IF formula which is equal to I F left parenthesis F3, greater than and equal to, ninety, comma, inverted comma, A, inverted comma, again comma, IF left parenthesis F3, greater than and equal to, eighty, comma, inverted comma, B, inverted comma, comma, IF left parenthesis F3, greater than equal to, seventy, comma, inverted comma, C, inverted comma, again comma, IF left parenthesis F3, greater than equal to, sixty, comma, inverted comma, D, inverted comma, comma, inverted comma, F, inverted comma right parenthesis four times.
Now copy this formula to other cells to assign grades to all students in one go. I would like to explain that you don’t need to write full equation such as greater than and equal to 80 but less than 90 because second IF expression will only be processed if the value being evaluated is less than ninety. If this was not the case, grade A would have assigned in first IF expression. This sequence goes on till F as we didn’t write that assign F to students with marks less than 60 because it is implied with the nested IF that if previous expressions are false, value is definitely less than sixty.
Example 2 Sales Commission. Assume that you have recently joined in the finance department of a company and your company pays commission to sales team against weekly sales made by the sales team. You have been asked to calculate sales commission for the last week sales so that same can be paid to the sales team. You can easily do this task by nested IF formula as we saw in the previous example.
You need to write correct syntax for nested IF according to current situation which is equal to I F left parenthesis F3, greater than and equal to, eight hundred, comma, ten percent, comma, again IF left parenthesis F3, greater than and equal to, seven hundred, comma, eight percent, comma, again IF left parenthesis F3, greater than and equal to, six hundred, six percent, comma, again IF left parenthesis F3, greater than and equal to, five hundred, comma, four percent, comma, zero percent right four parenthesis.
This syntax will calculate sales commission percentage based on which sales commission of employees can be calculated by simply multiplying sales commission percentage with weekly sales.
In this video, we briefly recalled what the basic IF function does and the syntax to use it and what makes it advanced IF or nested IF. Later on, two business scenarios were discussed to see how the advanced IF function can help in a real-life situation. I hope I explained it very well, but still, if you have any query, you can drop your question in the comments section.
If you learned something new from this video, I would be happy to know about this through the comments section. Hope you have liked the content by now. Please also share it with your friends too so that they can also learn something new.
Channel and Page links are available in description. Subscribe my channel on YouTube and click bell icon and also like my page on Facebook and follow. This way you will be able to keep track of my upcoming new videos. Link to related excel videos playlist is appearing on your YouTube screen now. Check these out for learning more.
Thank you for watching the video
================================================================================
In this video, Learn to use Excel IF function within IF function which is called Nested IF, also get to know where it can be used in real life situation. This will be done through a walkthrough of two real-life examples to understand how to use advanced IF function in a business scenario.
Share it with your friends to also let them know how to use the Nested IF function in Microsoft Excel.
Link for IF Function (Basic): https://youtu.be/rmNga7A5980
For more vidoes on tips for making your life easier, please subscribe me on YouTube and turn on notifications by pressing Bell Icon through YouTube Channel: https://www.youtube.com/user/greatbhatti
You can also find me on Facebook.
Facebook Page: https://fb.me/TheTechGeekOriginal
#HowToExcel #LogicalFormula #Tutorial #ExcelWithTheTechGeek
================================================================================
Nice Article Bro Check this website also>>>>>> https://excelxp.com/advance-conditional-formatting-excel-2016/
ReplyDeleteInteresting post and topic about basic Microsoft Excel.
ReplyDelete