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”
|
AND Function Test |
|||
|
Name |
Lab Score |
Test Score |
Result |
|
Anderson |
92 |
60 |
FALSE |
|
Bell |
85 |
79 |
TRUE |
|
Cartridge |
65 |
92 |
|
|
Delaney |
79 |
61 |
|
|
Finberg |
99 |
89 |
- 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