Logical functions in Excel with the examples of their use

Logic Functions in Excel check the data and return the result «TRUE» if the condition is true, and «FALSE» if not.

Consider the syntax of logic functions and examples of their application in the process of working with the Excel program.



Logical functions in Excel and examples of solving the problems

Objective 1. It is necessary to overestimate the trade balances. If he product is kept in stock for more than 8 months we should reduce its price 2 times.

We form a table with initial parameters:

To solve the problem, use the logical IF function. The formula will have a look like this: =IF(C2>=8;B2/2;B2)

Logical expression «C2>=8» is constructed using relational operators «>» and «=». The result of his calculations is a logical value of «TRUE» or «FALSE». In the first case, the function returns the value «B2/2». In the second it’s «B2».

Let’s complicate the task - to employ the logic function И. Now we have such a condition: if the product is stored for more than 8 months, the price is reduced 2 times. If it’s more than 5 months, but less than 8, it’s 1.5 times.

The formula takes the following form:

The use of logic functions in Excel:

Function Name	Value	Syntax	Note
TRUE	It does not have arguments, it returns the logical value " TRUE "	TRUE	It is rarely used as an independent function
FALSE	It does not have arguments, it returns the logical value " FALSE "	FALSE	-------//-------
AND	If all the given arguments return true result, then the function issues a logical expression " TRUE ". If one of the function outputs the result "false" the whole function gives the result "FALSE"	=AND(boolean1;boolean2;…)	Accepts up to 255 arguments in the form of conditions or links. It is mandatory to first.
OR	It displays the result “TRUE“ if at least one of the arguments is true.	=OR(boolean1;boolean2;…)	-------//-------
NOT	It changes the logic value “TRUE on the contrary - " FALSE ". And vice versa.	#NAME?	Usually it is combined with other operators.
IF	Test the validity of a logical expression and returns the result	=IF (boolean;value_if_TRUE;value_if_FALSE)	« boolean »  in the calculations should have results “ TRUE “ or “ FALSE “
IFERROR	If the first argument is true, it returns its argument. Otherwise it’s the value of the second argument.	= IFERROR(value;”error”)	Both arguments are mandatory.



The function IF can be used as arguments to the text values

Objective 2. If the value of the goods in the warehouse after the write-down was less than 300 r. or the product is stored for longer than 10 months, it is written off.

For the solution use logic functions IF and OR:

Condition recorded using a logical OR operation, stands as follows: goods are deducted if the number in cell D2 = 10.

In terms of non-compliance with the conditions, IF function returns to an empty cell.

As arguments, you can use other functions. These are mathematical, for example.

Objective 3. Students before entering the gymnasium pass math, English and Russian. Passing Score is 12. You should get at least 4 points in mathematics for admission. Create report on the receipt.

We set up a table with the source data:

It is necessary to compare the total number of points with a passing grade. And check that math score was not lower than "4". In the «result» column put "accepted" or "not accepted".

There’s such a formula:

Logical operator «AND» causes the function to check the validity of the two conditions. The mathematical function «SUM» is used to calculate the final score.

The function IF allows us to solve many problems; it is therefore used more often.

The statistical and logical functions in Excel

Task 1. Analyze the cost of cash balances after the devaluation. If the price of the product after reassessment is below average values, we should write off the product from the warehouse.

Working with the table in the previous section:

To solve the problem use the formula of such a form:

In logical terms «D2<AVERAGE (D2:D7)» apply statistical function AVERAGE. It returns the average of the range D2: D7.

Task 2. Find the average sales in stores.

We set up a table with the source data:

You need to find the arithmetic meaning for the cells, which value corresponds to a predetermined condition. That is to combine logical and statistical solution.

Below a table with a condition there’s the table to display the results.

We solve the problem with a single function:

The first argument $B$2:$B$7 is a range of cells for testing. The second argument B9 is a condition. The third argument the $C$2:$C$7 is an averaging range; the numerical values, which are taken to calculate the arithmetic mean.

AVERAGEIF function compares the value of B9 cells (№1) with values in the range B2: B7. That is the number of stores in the sales table. For matching the data considers the arithmetic mean of using numbers in the range C2: C7.

Task 3. Find the average sales in the store №1 New York and №2 Los Angeles.

Modify the table from the previous example:

It is necessary to fulfill two conditions - use the function of the form:

Download examples logical functions

The function AVERAGEIFS allows you to use more than one condition. The first argument $D$2:$D$7 is an averaging ranges (where are the numbers to find the arithmetic mean). The second argument $B$2:$B$7 is a range to test the first condition. The third argument B9 is the first condition. The fourth and fifth arguments are the ranges for checking the second condition respectively.

The function takes into account only the values that meet all these criteria.