+3 votes
34 views
in Sell ​​on the Internet by (76.4k points)
reopened
Y function in Excel: step by step tutorial with examples

1 Answer

+4 votes
by (506k points)
edited
 
Best answer

image

Y function in Excel: step by step tutorial with examples

The famous program Excel spreadsheet for Microsoft Office not only to organize numbers and text in tables clearly, but includes many features that can be used in a variety of situations, especially when the leaves contain many values. With its more than 400 options , the program allows you to edit or evaluate large amounts of data automatically. The AND function in Excel has always been one of the most popular. With this formula, you can define up to 255 conditions and check if the table values ​​meet them with just a few clicks. Next, we explain how to use the AND function in Excel step by step..

Index
  1. Y function in Excel: TRUE or FALSE?
  2. How to use the AND function in Excel step by step
    1. Step one: define the conditions
    2. Second step: apply AND from Excel
      1. Enter in cell
      2. Insert function via menu
    3. Third step: check all values ​​in the table
  3. Compare expected and actual values ​​with IF function
  4. Combine YES with Y in Excel

Y function in Excel: TRUE or FALSE?

The AND function is one of the logical functions of Excel, so it only allows you to check whether certain conditions are met or not . In other words, it can only return two results: if all conditions are met, Excel returns TRUE. If at least one of them is not fulfilled, the value that is expressed is FALSE.

How to use the AND function in Excel step by step

Suppose you have an Excel spreadsheet that contains many values ​​and you want to know which ones meet certain conditions. Finding them by hand would take a long time. Also, chances are that you overlooked some value and made mistakes. For this reason, you should use the AND function in Excel, the most suitable for automatically checking if certain conditions are met . Next, we explain how to apply it to an Excel spreadsheet step by step..

Step one: define the conditions

In this example, we want to know which employees meet all the requirements to get an incentive. For this, there are two conditions : they must have managed to attract at least three new clients and extend two or more contracts. This also implies, for example, if an employee has won five new customers (more than required), but has only succeeded in extending a contract, you will not receive the incentive. Using the AND function in Excel is ideal for this example, because the program can check if all the indicated conditions are optimally met.

image
To calculate the employee incentive with the Y function in Excel, you must first enter the values ​​for analysis.

Second step: apply AND from Excel

After you have defined all the values ​​for the AND function example, you can apply the formula to the table. For the sake of clarity, it is advisable to use different columns for the function and the result. There are two ways to enter the function:

Enter in cell

Enter the function in the cell where the result will be displayed . Functions always begin with an equal sign and the name of the function. The arguments of the function are enclosed in parentheses..

This is the basic syntax of the Excel AND function:

  =Y([valor_lógico1];[valor_lógico2];?)  
Note

If you write? And? in lower case, Excel will correct it automatically. Therefore, it is not necessary to pay attention to the spelling.

The logical values represent the conditions , which are separated by semicolons. The AND function supports between two and 255 conditions. However, it is not recommended to introduce too many: the more conditions a function has nested, the more complex it becomes, which complicates its creation, review and management.

To define logical values ​​or conditions, equals signs (=), greater than (>), and less than (<) are used. In our example, the first logical value represents the condition that the employee has obtained at least three new customers. Therefore, the value in column B must be equal to or greater than three. To define it, enter? B2> = 3 ?. Logical value 2 is the condition that the employee has also extended at least two contracts. To do this, write? C2> = 2 ?. This is how the complete AND function would look in our example:

  =Y(B2>=3;C2>=2)  
image
If you prefer, you can enter the AND function directly into the cell.

Press the return key on your keyboard to confirm the formula. When you do, Excel will no longer display the function itself, but the result. In our example, the first employee meets only one of two requirements. Therefore, the value FALSE is generated:

image
Cell D2 no longer shows the underlying AND function, but only its result

Insert function via menu

If you prefer, there is the option of entering the Y function in Excel through a dialog box. To open it, select the menu? Formulas? and hit? Insert function? or left click on the corresponding icon in the toolbar:

image
There are several ways to open the dialog and enter the Y function in Excel via the menu

Excel opens the ? Insert Function? Dialog box. . To select the logical function AND you must select the category? Logic? in the drop-down list. When you select it, it will be marked in blue. Confirm the section by clicking on? Accept ?

image
And it is classified among the logical formulas, along with OR or IF. If you don't adjust the category first, it won't be listed below.

If the function you want is not in the list, select? Everything ? in the drop-down menu of? Select category ?. Excel will show you a list of all available functions , through which you can navigate with the keyboard arrows to choose the "AND" option. Click on the function with the left mouse button to mark it in blue and confirm with? Accept ?

image
The AND function is part of the more than 400 Excel functions.

Then Excel opens the ? Function Arguments? Dialog box. , where the arguments are defined on separate lines . During this process, the program already shows if the conditions are met. When you have entered them all, confirm them by clicking on "OK". Excel creates the formula and displays the result in the cell.

image
Enter the logical values ​​you want to set for the AND function in Excel.

Third step: check all values ​​in the table

It is not necessary to insert the AND function by hand on each line. If the conditions you entered apply to all values ​​(in our example, all employees), Excel can pass the function to all rows with just a few clicks .

To do this, first select the cell that contains the AND function. Then, left-click on the green square that appears on the border of the cell. Hold down the button and drag the function over all the cells to which it has to be applied. In our example, it will be from cell D2 (employee 1) to D11 (employee 10).

image
Click the green square and drag it over the other cells to transfer the Excel Y function.

Excel transfers the AND function automatically and adapts the formula accordingly , checking if the conditions are met for each employee:

image
Excel shows if conditions are met for all employees

Compare expected and actual values ​​with IF function

With the IF function, you can check if a requested value matches an expected value. The basic syntax of this formula is as follows:

 

  =SI(prueba_lógica;[valor_si_verdadero];[valor_si_falso])  

The IF function is very similar to AND. Its two most important differences are the following:

  • SI checks if a single condition is met .
  • With the IF formula you can customize the results that are displayed .

With the AND function in Excel, the only possible results are TRUE or FALSE. In the case of SI, on the other hand, the parameters? Value_if_true? and ? value_if_false? to define the results that Excel displays in the cell after the calculation is done.

advice

The SUMIF function offers the possibility of adding only some values. If you are interested, you can learn how to use it in our article on the SUMIF function.

In our example table, you can use the IF function to define these conditions: if an employee gets at least five new customers, they will receive an incentive of 100 euros. If the employee does not reach the goal, he will not get any incentive. The IF formula would be as follows:

  =SI(B2>=5;100;0)  
image
Like the rest of the functions, you can also enter the IF function directly in the cell.

Combine YES with Y in Excel

Excel allows you to combine multiple functions into a single formula . Combining the IF and AND functions offers two benefits : defining multiple conditions and customizing the results that are displayed.

This would be the basic syntax that results from combining the two functions:

  =SI(Y([valor_lógico1];[valor_lógico2];[valor_lógico3];?);[valor_si_verdadero];[valor_si_falso])  

In our example, the actual formula would have the following structure:

  =SI(Y(B2>=3;C2>=2);100;0)  
image
Excel allows you to combine multiple functions into a single formula

Once the combined formula is confirmed, we get the following return values ​​in our example table:

image
Excel table with the results.

...