Subscribe my youtube channel!

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:

=IF(OR(AVERAGE(B2:C2)>=0.5;AND(B2>0.45;C2>0.45));”Approved”;”Not approved”)

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%):

OR(AVERAGE(B2:C2)>=0.5;AND(B2>0.45;C2>0.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:

=IF(OR(SUM(B2:C2)>=1;MIN(B2:C2)>0.45);”Aproved”;”Not aproved”)

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):

OR(SUM(B2:C2)>=1;MIN(B2:C2)>0.45)

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:

=IF(OR(AVERAGE(B2:C2)>=0.5;MAX(B2:C2)>0.85);”Aproved”;”Not aproved”)

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):

OR(AVERAGE(B2:C2)>=0.5;MAX(B2:C2)>0.85

Final result is:

### Like this:

Like Loading...