Artikel zum Thema:
Schließen
Changelog
Schließen

Using the IF and AND functions in excel

Introduction

Spreadsheet Editor offers you over 440 built-in functions that you can use when working with your spreadsheet data. This article explains how to use the AND function.

The AND function is one of the logical functions that is used to check if the logical value you enter is TRUE or FALSE.

The function returns TRUE if all the arguments are TRUE and returns FALSE if at least one of the arguments is FALSE.

It is commonly used to check several conditions at once and can be added as an argument of other logical statements, for example the IF function.

The AND function has the following syntax:

AND(logical1, logical2, ...)

where 

  • logical1 is the first condition you want to check whether it is TRUE or FALSE. It's a required argument.
  • logical2, ... logicalN are additional conditions to check. These are optional arguments.

Step 1. Apply the AND function

To apply the AND function, follow these steps:

  1. select the cell where you wish to display the result,
  2. switch to the Formula tab,
  3. click the Logical button,
  4. choose the AND function from the list,
  5. enter the required arguments separating them by commas.
if and excel Step 1 if and excel Step 1

Alternatively, you can add the AND function in one of the following ways:

  1. start adding the function:
    • switch to the Home tab, click the Insert Function Insert function icon icon situated at the top toolbar and select the Additional option,
    • or right-click within a selected cell and select the Insert Function option from the contextual menu,
    • or click the Function icon icon situated before the formula bar,
  2. in the Insert Function window, select the Logical function group from the list,
  3. click the AND function and press the OK button,
  4. enter the required arguments separating them by commas.

Note: it's possible to add up to 255 arguments.

Values can be entered manually or included into the cell (or range of cells) you make reference to. The arguments must be logical values (i.e. TRUE or FALSE) or logical expressions (e.g. 10<100) that can be evaluated as TRUE or FALSE. Text values and empty cells used as arguments are ignored. 0 is interpreted as FALSE, all other numeric values are interpreted as TRUE.

if and excel Step 1 if and excel Step 1

When the arguments are specified, press the Enter key. The result will be displayed in the selected cell.

In our example, we test if expenses are less than revenue for each of the three months.

=AND(B2<C2,B3<C3,B4<C4)
if and excel Step 1 if and excel Step 1

As all the logical arguments are evaluated as TRUE, the AND function returns TRUE.

If we change the value in cell C4 so that the third condition (B4<C4) becomes FALSE, while the first and second conditions remain TRUE, the AND function returns FALSE.

if and excel Step 1 if and excel Step 1

Step 2. Apply the IF function

If you want to take a specific action depending on whether several conditions are TRUE or not, use the IF function in combination with the AND function.

The IF function has the following syntax:

IF(logical_test, value_if_true, value_if_false)

The AND function is added as the logical_test argument of the IF function. For example, this can be done in the following way:

=IF(AND(B5<C5,D5>=200),"success","failure")
if and excel Step 2 if and excel Step 2

In this example, if total expenses are less than total revenue (B5<C5), and the total net profit value is greater than or equal to the specified value (D5>=200) at the same time, the IF function should return the "success" value. As the second condition is not met, the IF function returns the "failure" value.

if and excel Step 2 if and excel Step 2

That's all. Now you know how to use the AND function when processing your spreadsheet data.

How to create a new document?
Schließen
Try now for free Try and make your decision No need to install anything
to see all the features in action
Wer sich dafür interessiert hat,
hat auch das Folgende gelesen:
Schließen