In this post i will cover the logical function IF and some logical/math/statistic functions that will help to exercise resolution.
Starting with the table exercise, i have the result of 2 tests for 4 different students, and i want to classify each student as “approved” or “not approved”, depending of the defined conditions (condition 1 and condition 2):
In the first condition, to be approved, the student must have the average of the 2 tests positive or the 2 tests above 45%. Now i’m gonna write these condition in excel:
I have 2 possible values (“approved” and “not approved”), so i use de IF function that help me to choose one of the two situations:
My logical test is inside OR function because i have 2 possible ways to be approved using the condition 1 (average of 2 tests equal or greater then 50% OR the 2 tests are greater than 45% – use of AND function because we need the 2 tests greater then 45%):
If value_if_true appear “Approved” and value_if_false “Not approved”
This is the version v1 for the first condition.
Now i’m gonna make the second version for the same condition:
The logical_test will be the sum of the 2 tests that must be equal or greater then 100% or the lowest test rate must be higher then 0.45 (the final results are the same like we expected):
In the condition 2, the students are approved if the test average is positive or if one of the two tests is above 85%:
For this situation the excel function is:
Where the average needs the be greater or equal to 50% (average positive) or one of the tests must be above 85% (i used the MAX function to see if the best test is above 85% – it’s one of the possible solutions):
Final result is: