10 AND function for Spreadsheets: Information

OVERVIEW:

Where to find AND function:  FORMULA tab, Logical in the Function group, then find AND in the pull down menu.

Purpose of AND function:  To evaluate all of its arguments.  It returns, or displays  True for every logical test in the formula..

Return Value – the result of the evaluation is TRUE or FALSE

Syntax:  =AND (logical test, [value all values TRUE],[if one or more value is not true FALSE)

Arguments:

Logical test:  A value or logical expression that can be evaluated as TRUE or FALSE

Value if true:  [optional]  The value to return when logical test evaluates all to TRUE.

Value if false: [optional]  The value to return when logical test evaluates one or more is FALSE

EXERCISE (This isn’t graded, but you might find it useful before completing your assignment.)

If evaluating a student based on lab scores and test scores, this function will automatically assign a True or False to that student.  You would be testing the scores themselves for each student and then returning either “True” or “False”

• Create a little spreadsheet like the one above with the headings Name, Scores and Results.  Type in five or six students and give them scores – you don’t have to use the same ones as I did.  Also you will not have any “Results” until you put the formula in.
• Click in cell D4 (in this example) to insert the formula result.  Click on the FORMULA TAB, then click the Logical icon from the Function Library Group.  Once you click on the Logical icon a menu appears with various Logical functions. Choose AND.  The result is an argument box as seen below.  Argument boxes are easier to work with because they show you where to put the information needed to complete the test.

• We need to complete two logical tests.  The first box (Logical Test) is asking for information for the lab scores.  We want to set parameters on what is a passing grade.  In that logical box, click cell B5 on the spreadsheet you created (first score of the people you entered.).  Next put in the symbols (in the same box) that mean greater than and equal to (>=).  This is followed by a grade of 70.  (See other Logical Operators at the bottom of this page.)
• In the second box (Logical Test) click
  cell C3 on the spreadsheet you created.  Next put in the symbol (in the same box) that means greater than or equal to (>=), follow by the grade 70.  Then click ENTER.  If both instances are true (both the lab score and test score are greater than or equal than 70), the result will be TRUE.  If one or both are not greater than 70, the result will be FALSE.
• Another logical test box may open, but you need only complete two of them.
• If one of the two scores is not greater than or equal to 70, the result will be FALSE.
• The formula in the formal function bar will be =AND(B3>=70,C3>+70)

Only complete the first two logical tests for this example; although you can use several when using the AND function.

Only complete the first two logical tests for this example; although you can use several when using the AND function.

• Click OK.  The formula now appears in cell D4 and on the formula bar.   

• Pull the formula down for the other students and watch as either “TRUE” or “FALSE” appears for each student.

Notes:  More Logical Operators