4) CONDITIONAL (IF) AND BOOLEAN OPERATOR

CONDITIONAL (IF) AND BOOLEAN OPERATOR

IF(Condition)

Specifies a logical test to be performed.

Formula has to given as :

IF(Test; ThenValue; OtherwiseValue)

Where,

Test is any value or expression that can be TRUE or FALSE.

ThenValue (optional) is the value that is returned if the logical test is TRUE.

OtherwiseValue (optional) is the value that is returned if the logical test is FALSE.

In the LibreOffice Calc functions, parameters marked as "Optional" can be left out only when no parameter follows.

An if statement in calc is written as :

=if(test_something,do_if_thats_true,do_if_thats_false)

If our test_something is true, calculate the do_if_thats_true section. If our test_something returns false, then calculate the do_if_thats_false section.

Example :

To know the result (pass/fail) of each student by using the condition(For eg. percentage to be greater than 40%) . Follow the steps which are performed with the help of spreadsheet given as Handout 1.

1. Give Title to the field/column (pass/fail)
2. Select the cell where you want the solution to be displayed.
3. Go to Insert menu →function →select logical function→ if

4. Now give your condition as per your problem (for e.g. IF (percentage i.e. J4 >40, then the student is pass otherwise he/she fails))

5. Click OK to see the desired result and then paste the formula (refer Handout 4 ) in all cell related to each student. You will see the result of each student.

BOOLEAN OPERATOR (AND/OR)

If you want to expand your condition you can use the Boolean operator AND /OR , it termed as boolean as both operator returns only True or False as a result of their calculation.

AND

It returns TRUE if all arguments are TRUE. If one of the elements is FALSE, this function returns the FALSE value.

The arguments are either logical expressions themselves (TRUE, 1<5, 2+3=7, B8<10) that return logical values, or arrays (A1:C3) containing logical values.

Formula can be written as :

AND (LogicalValue1; LogicalValue2 ...LogicalValue30)

LogicalValue1; LogicalValue2 ...LogicalValue30 are conditions to be checked. All conditions can be either TRUE or FALSE. If a range is entered as a parameter, the function uses the value from the range that is in the current column or row. The result is TRUE if the logical value in all cells within the cell range is TRUE.

For Example:

The logical values of entries 12<13; 14>12, and 7<6 are to be checked:

=AND(12<13;14>12;7<6) returns FALSE.

=AND (FALSE;TRUE) returns FALSE.

Example 1:

You want to know a that do you have any girl whose marks is above 80% and submit her project.

To perform the use of AND function we are using the spreadsheet given in Handout 1.

Steps to use AND:

1. Select the cell where you want the solution to be displayed.
2. Either type the whole formula in the desired cell =AND(J4>80,K4="FEMALE",H4="PROJECT SUBMIT")
3. Press enter to see the result

OR

Goto INSERT MENU →FUNCTION → LOGICAL FUNCTION →AND

4. Give all logical values which you want to check (for eg. % to be greater than 80 , gender to be female and project submitted or not).

5. Click OK to see the result and then copy formula in other cells corresponding to that column and see the result .

5. False says I am not satisfying the given condition and True says I satisfy the condition In above example we have only one true condition .

OR

It returns TRUE if at least one argument is TRUE. This function returns the value FALSE, if all the arguments have the logical value FALSE. It returns true if one of the condition is true .

Formula can be used as:

OR(LogicalValue1; LogicalValue2 ...LogicalValue30)

Where

LogicalValue1; LogicalValue2 ...LogicalValue30 are conditions to be checked.

All conditions can be either TRUE or FALSE. If a range is entered as a parameter, the function uses the value from the range that is in the current column or row.

Example:1

The logical values of entries 12<11; 13>22, and 45=45 are to be checked.

=OR(12<11;13>22;45=45) returns TRUE.

=OR(FALSE;TRUE) returns TRUE.

Example:2

Steps to use OR:

1. Select the cell where you want the solution to be displayed.
2. Either type the whole formula in the desired cell =OR(J4>80,K4="FEMALE",H4="PROJECT SUBMIT")
3. Press enter to see the result

OR

Goto INSERT MENU →FUNCTION → LOGICAL FUNCTION →OR

4.Give all logical values which you want to check (for e.g. % to be greater than 80 , gender to be female and project submitted or not).

5. Click OK to see the result and then copy formula (Refer Handout 5)in other cells corresponding to that column and see the result .

6. False says I am not satisfying the given condition and True says I satisfy the condition In above example we have only 6 true values condition .