IF Statement and logical/math/statistic functions

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

if_statement

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:

if_statement_1

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”

if_statement_3

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)

if_statement_4

In the condition 2, the students are approved if the test average is positive or if one of the two tests is above 85%:

if_statement_5

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:

if_statement_6

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s