Skip to main content
Workforce LibreTexts

4.5: AND function for Spreadsheets- Information

  • Page ID
    13031
  • \( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)

    \( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)

    \( \newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\)

    ( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\)

    \( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\)

    \( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\)

    \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\)

    \( \newcommand{\Span}{\mathrm{span}}\)

    \( \newcommand{\id}{\mathrm{id}}\)

    \( \newcommand{\Span}{\mathrm{span}}\)

    \( \newcommand{\kernel}{\mathrm{null}\,}\)

    \( \newcommand{\range}{\mathrm{range}\,}\)

    \( \newcommand{\RealPart}{\mathrm{Re}}\)

    \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\)

    \( \newcommand{\Argument}{\mathrm{Arg}}\)

    \( \newcommand{\norm}[1]{\| #1 \|}\)

    \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\)

    \( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\AA}{\unicode[.8,0]{x212B}}\)

    \( \newcommand{\vectorA}[1]{\vec{#1}}      % arrow\)

    \( \newcommand{\vectorAt}[1]{\vec{\text{#1}}}      % arrow\)

    \( \newcommand{\vectorB}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)

    \( \newcommand{\vectorC}[1]{\textbf{#1}} \)

    \( \newcommand{\vectorD}[1]{\overrightarrow{#1}} \)

    \( \newcommand{\vectorDt}[1]{\overrightarrow{\text{#1}}} \)

    \( \newcommand{\vectE}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash{\mathbf {#1}}}} \)

    \( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)

    \( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)

    \(\newcommand{\avec}{\mathbf a}\) \(\newcommand{\bvec}{\mathbf b}\) \(\newcommand{\cvec}{\mathbf c}\) \(\newcommand{\dvec}{\mathbf d}\) \(\newcommand{\dtil}{\widetilde{\mathbf d}}\) \(\newcommand{\evec}{\mathbf e}\) \(\newcommand{\fvec}{\mathbf f}\) \(\newcommand{\nvec}{\mathbf n}\) \(\newcommand{\pvec}{\mathbf p}\) \(\newcommand{\qvec}{\mathbf q}\) \(\newcommand{\svec}{\mathbf s}\) \(\newcommand{\tvec}{\mathbf t}\) \(\newcommand{\uvec}{\mathbf u}\) \(\newcommand{\vvec}{\mathbf v}\) \(\newcommand{\wvec}{\mathbf w}\) \(\newcommand{\xvec}{\mathbf x}\) \(\newcommand{\yvec}{\mathbf y}\) \(\newcommand{\zvec}{\mathbf z}\) \(\newcommand{\rvec}{\mathbf r}\) \(\newcommand{\mvec}{\mathbf m}\) \(\newcommand{\zerovec}{\mathbf 0}\) \(\newcommand{\onevec}{\mathbf 1}\) \(\newcommand{\real}{\mathbb R}\) \(\newcommand{\twovec}[2]{\left[\begin{array}{r}#1 \\ #2 \end{array}\right]}\) \(\newcommand{\ctwovec}[2]{\left[\begin{array}{c}#1 \\ #2 \end{array}\right]}\) \(\newcommand{\threevec}[3]{\left[\begin{array}{r}#1 \\ #2 \\ #3 \end{array}\right]}\) \(\newcommand{\cthreevec}[3]{\left[\begin{array}{c}#1 \\ #2 \\ #3 \end{array}\right]}\) \(\newcommand{\fourvec}[4]{\left[\begin{array}{r}#1 \\ #2 \\ #3 \\ #4 \end{array}\right]}\) \(\newcommand{\cfourvec}[4]{\left[\begin{array}{c}#1 \\ #2 \\ #3 \\ #4 \end{array}\right]}\) \(\newcommand{\fivevec}[5]{\left[\begin{array}{r}#1 \\ #2 \\ #3 \\ #4 \\ #5 \\ \end{array}\right]}\) \(\newcommand{\cfivevec}[5]{\left[\begin{array}{c}#1 \\ #2 \\ #3 \\ #4 \\ #5 \\ \end{array}\right]}\) \(\newcommand{\mattwo}[4]{\left[\begin{array}{rr}#1 \amp #2 \\ #3 \amp #4 \\ \end{array}\right]}\) \(\newcommand{\laspan}[1]{\text{Span}\{#1\}}\) \(\newcommand{\bcal}{\cal B}\) \(\newcommand{\ccal}{\cal C}\) \(\newcommand{\scal}{\cal S}\) \(\newcommand{\wcal}{\cal W}\) \(\newcommand{\ecal}{\cal E}\) \(\newcommand{\coords}[2]{\left\{#1\right\}_{#2}}\) \(\newcommand{\gray}[1]{\color{gray}{#1}}\) \(\newcommand{\lgray}[1]{\color{lightgray}{#1}}\) \(\newcommand{\rank}{\operatorname{rank}}\) \(\newcommand{\row}{\text{Row}}\) \(\newcommand{\col}{\text{Col}}\) \(\renewcommand{\row}{\text{Row}}\) \(\newcommand{\nul}{\text{Nul}}\) \(\newcommand{\var}{\text{Var}}\) \(\newcommand{\corr}{\text{corr}}\) \(\newcommand{\len}[1]{\left|#1\right|}\) \(\newcommand{\bbar}{\overline{\bvec}}\) \(\newcommand{\bhat}{\widehat{\bvec}}\) \(\newcommand{\bperp}{\bvec^\perp}\) \(\newcommand{\xhat}{\widehat{\xvec}}\) \(\newcommand{\vhat}{\widehat{\vvec}}\) \(\newcommand{\uhat}{\widehat{\uvec}}\) \(\newcommand{\what}{\widehat{\wvec}}\) \(\newcommand{\Sighat}{\widehat{\Sigma}}\) \(\newcommand{\lt}{<}\) \(\newcommand{\gt}{>}\) \(\newcommand{\amp}{&}\) \(\definecolor{fillinmathshade}{gray}{0.9}\)

    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 cellB5 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
      cellC3 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

    Comparison Operator

    Meaning

    Example

    =

    Equal to

    A1=D1

    >

    Greater than

    A1>D1

    >=

    Greater than or equal to

    A1>=D1

    <

    Less than

    A1<D1

    <=

    Less than or equal to

    A1<=D1

    <>

    Not equal to

    A1<>D1

    CC licensed content, Shared previously

    4.5: AND function for Spreadsheets- Information is shared under a not declared license and was authored, remixed, and/or curated by LibreTexts.

    • Was this article helpful?