Method 1 – Calculate Grade with Nested IF Function
Steps:
- Our table looks like the picture below, where we want to obtain the equivalent grades.
- Write this formula in cell D5 and drag this formulated cell to the rest of Column D. By doing this you will get the result for the whole column.
=IF(C5<50,"F",IF(C5<60,"D",IF(C5<65,"C",IF(C5<70,"C+",IF(C5<75,"B",IF(C5<80,"B+",IF(C5<85,"A-",IF(C5<90,"A","A+"))))))))
Note: Maintain proper order while defining conditions (lowest to highest/highest to lowest)
Method 2 – Apply IF Function with Triple Conditions
If you want to allocate some students in the thesis/project program. The conditions are:
Condition 1: The student has to obtain a CGPA of more than 2.50 (must be fulfilled)
And Condition 2: Has to earn a total number of credits more than or equal to 110,
Or Condition 3: Has to complete at least 1 elective course.
A student must fulfill the 1st condition. After fulfilling the 1st condition, either the 2nd or 3rd condition needs to be fulfilled to be eligible to take the thesis/project.
Write the below formula in Cell F5 and copy this formulated cell to the rest of column F where you want to find your result.
=IF(AND(C5>=2.5,OR(D5>=110,E5>=1)), "Yes","No")Formula Breakdown:
- If all conditions are matched, then the AND function returns TRUE otherwise
- If any condition is matched, then the OR function returns TRUE otherwise, FALSE
- AND(C5>=2.5,OR(D5>=110,E5>=1)) satisfied by the IF function then it returns YES otherwise NO.
By doing this you will get the desired result that you were looking for.
Method 3 – Use Multiple IF-AND Statements in Excel
Step 1:
- We are going to use the IF and AND functions in one complex formula to determine the route each respective patient must take in the hospital.
- In Cell E5 type the following formula:
=IF(AND(C6="Yes",D6="Tuberculosis"), "Patient sent to Ward One, then respiratory department",IF(AND(C6="Yes",D6="Kaposi's sarcoma"),"Patient sent to Ward One, then oncology department",IF(AND(C6="Yes",D6="Cardiovascular disease"),"Patient sent to Ward One, then cardiology department","Bypass Ward One and go directly to department")))
- Press CTRL-ENTER to get the formula to deliver the route needed, given the conditions in Cell C5 and D5 that the formula uses for evaluation. We see that if the patient has HIV/AIDS and opportunistic infection of Tuberculosis, this patient must first go to Ward One and then to the respiratory department of the hospital. The formula has delivered the correct result.
Step 2:
- AutoFill the functions to the rest of the cells in column E. The correct route for each registered patient can be identified by the formula.
Things to Remember While Working With Multiple IFs
You need to be aware of the following things while working with multiple IF functions.
- In Excel 2007 – 2016, a total of 64 conditions can nest up while working with multiple IFs.
- You have to maintain a proper order while working with multiple IFs.
- If your formula contains too many IFs, it’s better to use OR and AND functions with that. The OR and AND functions usually reduce the formula size. You can also use the CONCATENATE function. If necessary, VLOOKUP, LOOKUP, and INDEX/MATCH. These functions can also be used instead of multiple functions.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Related Articles
<< Go Back to Excel IF Function | Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
Hi Kawser Thanks for the blog.
I would like to make an adjustment herein . When the HIV/AIDS Condition is “No” in your sheet , the patient is requested to “Bypass Ward One and go directly to department”. Here, the name of the specific department is not mentioned (respiratory, cardiology or otherwise).
However if you modify the formula as follows, the patient is requested to bypass ward one and go to the specific department (respiratory, cardiology , Emphysema treatment):
=IF(AND(B5=”Yes”,C5=”Tuberculosis”),”Patient sent to Ward One, then respiratory department”,IF(AND(B5=”Yes”,C5=”Kaposi’s sarcoma”),”Patient sent to Ward One, then oncology department”,IF(AND(B5=”Yes”,C5=”Cardiovascular disease”),”Patient sent to Ward One, then cardiology department”,IF(AND(B5=”No”,C5=”Tuberculosis”),”By Pass ward one and go directly to respiratory department”,IF(AND(B5=”No”,C5=”Emphysema”),”Bypass Ward One and go directly to Emphysema treatment department”,IF(AND(B5=”No”,C5=”Kaposi’s sarcoma”),”Bypass ward one and go directly to oncology department”,”Bypass Ward one and directly go to cardiology department”))))))
Hope you got it :):):):)
Thank you for your contribution. The modification of the formula, you suggested, would work for additional detailed routes that the hospital in question, may feel they need to add.
We can lead on from this and design an Excel Sheet with VBA using Nested IFS and a small picture of the routes, for the ten most common routes patients at a certain hospital need to take based on the most often noted diseases and disease combinations. So I will include the routes you specified in the Visual Basic Application.
So look out for that post.
I am putting the same formula , so the place on No HIV/AIDS, its showing FALSE.
Why???
Syed,
Sometimes when the formula is posted in WordPress platform, it might be changed. Please, download the working file and check the formula there.
Dear Kawser,
Thanks for the blog.very nice and helpful.
regarding (If Function with 3 Conditions) I think the conditions should be like this:
The student has to obtain a CGPA more than 2.50.
He has to earn a total number of credits more than or equal to 110
He has to complete at least 1 elective courses
The student must fulfill the three conditions, not just the 1st and 2nd, or 1st and 3rd
Am I right?
regards
Hi Ahmad,
You’re welcome. Glad our blog posts help you master Excel topics.
I have updated this part. For your clarification: the condition 1 must be fulfilled to be eligible to take the thesis work/project. Of the last conditions, students must fulfill one condition. I hope this makes things clear to you.
Best regards
Kawser
Thanks a lot Mr kawser, now it is very clear, and thanks for the update.
Thanks a lot Mr. Kawer, now it is very clear.
also, thanks for the update.
regards
Ahmad