Know the conditional functions in Excel

54
22716
Excel Spreadsheets

Conditional functions in Excel help us to make decisions. They can be combined and nested (you can place a function inside another function) depending on the situation. Let's know each one of them, but first let's see the table of logical operators:

Excel Online Course

The logical operators are:

> Greater

Excel Spreadsheets

<Smaller

> = Greater Equal

<= Equal Minor

Equal

<> Different

Every conditional function depends on logical testing. The idea is that the function returns you a value depending on a criteria you set. These operators will be used in these functions in their logical tests and conditions.

Function E

The function E returns us TRUE if all logical tests are true, or false if one of them is false. Let's exemplify:

Function Syntax E is: = E (Logical; ...; Logical)

We can do several logical tests on the function. See example:

=E(1<30;30<>20;32>10)

Know the conditional functions in Excel 1

In this example the function will return TRUE because 1 is smaller than 30 and 30 is different from 20 and 32 is larger than 10, all tests are true, so the function return will be TRUE.

In this example:

=E(1>30;30<>20;32>10)

The function return will be FALSE because 1 is not greater than 30, when one of the tests is not true, the function return will be FALSE.

We can conclude that the function E will only return TRUE when all tests are true. When you go into logical tests of your spreadsheet, ideally you should use cell references instead of putting absolute values ​​in your calculations, so that whenever you change a value, the function will be recalculated and give a new answer.

Excel Online Course

Function OR

The OR function behaves contrary to the E function if only one of the logic tests is TRUE, it will return TRUE, even if the others are false. Its syntax is as follows:

= OR (Logical; ...; Logical)

See example:

=OU(10>40;45<>50;6<3)

Know the conditional functions in Excel 2

The return of the function in the example will be TRUE because one of the logical tests is true, see: 10 is not larger than 40, but 45 is different from 50, the result of that test is enough for the return function TRUE since 6 is not smaller than 3.

In this example:

=OU(10>40;45>50;6<3)

The return from the above example will be FALSE because all logic tests are false. See: 10 is not greater than 40; 45 is not greater than 50; 6 is not smaller than 3.

We can conclude that the function OU will only return FALSE when all tests are false.

Function SE

With the SE function differently from the E ou OU, in addition to testing whether the condition is true or false, we can tell her what to do in each situation, whether she will perform a calculation or whether she will return a message. It will give us the ability to automate our worksheet intelligently.

Its syntax is: = SE (logical_test, True_Value, True_value)

Logical_test: as in the OR and E functions in the IF function we have a logical test that will determine if the situation is true or false, this test can compare two values ​​that we can indicate in the following ways: 10> 20; A2 <> B2; 10> C2. We can both use absolute values ​​as reference of cells.

Valor_se_Verdadeiro: is what the function will return if the logical_test be true, we can indicate a calculation, reference a cell, or even display a message. In the case of the message the text should be enclosed in quotation marks as in the examples:

A2; (A2 + A4); "This is the Greatest Value";

Value_se_Falso: is what the function will return if the logical_test whether false or true, we can indicate a calculation, reference a cell or even present a message, in this case the message must also be enclosed in quotation marks, besides also being able to use the operator FALSE which is reported without quotation marks as in the examples:

A2; (A2 + A4); "This is the Greatest Value"; FALSE

Combining functions

The function SE gives us the possibility of combining it with the function E ou OU. Let's assume that we need to have several logical tests, and all the true results to return the value_is true, for this we can combine the function SE with the function E. Or that we need only a true value, in this case we can combine the function SE with the function OU. Let's use these functions within the function SE No. logical_test. See the examples:

= SE (E (A2 <10; B2 <20); "Lower price than all competitors"; "Higher price than competitors")

Know the conditional functions in Excel 3

= SE (OR (A2 <10; B2 <20); "Low price"; "High price")

Note that the equal sign appears only before the function SE and does not follow the other functions.

Is nested

The nested SE is the use of the SE function within the SE function. We can use nested SE when we have two or more distinct logical tests to perform. Let's suppose that if the student took an average> than 7 it is approved, but its faults can also not be greater than 6.

Student Video Failures Result
John 8 7 Disapproved
Maria 10 0 Approved
Pedro 7 6 Okay

To solve this situation, our function would look like this:

In the first logical_test we will check if the student has passed the average, then if approved by the average, we will insert another function SE No. value_is true and test if it has not failed for faults, which would look like this:

= SE (B2> = 7;SE (C2 <= 6; "Approved"; "Disapproved");"Disapproved")

We can also nest other functions SE always in value_is true of each one or even in the value_se_false according to the necessity.

Know the conditional functions in Excel 4

Already the nested functions are not limited to the function SE, we can insert another function in the value_is true as PROCH ou PROCV, among other applications.

Excel Online Course

Were these tips helpful to you? Want to learn other Excel features? Keep following our blog to find out everything you can do with this tool!

Excel Spreadsheets

54 COMMENTS

  1. I would like to know if it is possible to create a formula that, by a certain criterion, passes data from one book from one foha to another, ie:

    List with multiple process names in “active” state
    when I go to the "ceased" state, the formula must transpose the data on another sheet of the same book, so that the "active" on one sheet and the "ceased" on the other

  2. Good afternoon,

    I was able to do it using it on itself and I used the somase to calculate the score.

    Thank you.

  3. Hi Alexandro, I would create an auxiliary column to show the score of the product sold (you can use SE within SE for this or PROCV). Then just create a list with the name of the sellers and use a CONT.SE to count the auxiliary column with the values, whenever the seller's column is from the seller in question in that auxiliary table

  4. Good morning,

    I would like your help, I have a sales spreadsheet where each product has a score, so I would like to assign the total amount of points purchased by a seller on each product that he sold, EX: column C will be the column of the products and column D will be that of the sellers. EXAMPLE below: of the products reported below we know that Application = 1, Insurance = 3, Savings = 1, Social Security = 2
    CD
    (Product) (SELLER)
    ALEX APPLICATION
    INSURANCE JOÃO
    SAVING SAVINGS
    MARCELO PREVENTION

    In this case the result would be, ALEX WITH 2 POINTS, JOHN WITH 3 POINTS AND MARCELO WITH 2 POINTS.
    I would like to know how to make this worksheet where it looks in Column C for the product type and then searches the worksheet D which seller and plays the total points of each vendor at the end of the worksheet.

    I thank

  5. Hi Luis, you can use PROCV with TRUE at the end to get approximate values

  6. Hello, I need some help. I have an 1 table of equivalence in the cell a1 until a5 these are the values ​​R $ 1.099,00 R $ 2.099,00 R $ 2.599,00
    R $ 3.099,00 ai in cell b1 to b5 90 120 150 180 200 according to each of the cell values ​​a1 to a5, so I have a 1 worksheet that adding values ​​go to the 2 worksheet these values ​​added will be according to the summation of the 1 worksheet, which, if added, will give a value that is less than or equal to the value of the 1 table from a1 to a5 and according to the displayed value of the sum, this value shows the value according to the table 1 b1 to b5, This is my problem, someone who can help me, att LUIS

  7. I believe (not sure) that you can create a table with ages and formulas in a second column. Then just use a PROCV with INDIRECT to, according to age, calculate one of the formulas

  8. Hello, I need some help.
    I am trying to create a spreadsheet in which depending on the age, will inform me a value according to the parameter of that age. In the cell B1 I put the age and according to the result of the mobile H6, will inform the result. Example:

    =SE(B1<=29;SE(H6<0,83;"BAIXO";SE(H6<0,88;"MODERADO";SE(H6<0,94;"ALTO";"MUITO ALTO"))))
    =SE(B1<=39;SE(H6<0,84;"BAIXO";SE(H6<0,91;"MODERADO";SE(H6<0,96;"ALTO";"MUITO ALTO"))))
    =SE(B1<=49;SE(H6<0,88;"BAIXO";SE(H6<0,95;"MODERADO";SE(H6<1;"ALTO";"MUITO ALTO"))))
    =SE(B1<=59;SE(H6<0,9;"BAIXO";SE(H6<0,96;"MODERADO";SE(H6<1,02;"ALTO";"MUITO ALTO"))))
    =SE(B1<=69;SE(H6<0,91;"BAIXO";SE(H6<0,96;"MODERADO";SE(H6<1,03;"ALTO";"MUITO ALTO"))))

    I managed to do it, but in separate formulas, I would like to put all of these together. Then when I put the person's age, automatically calculate the result.

  9. Hi Vinícius, the best way to learn how to read formulas is to see how they work in the FORMULAS tab, AUDIT group, VALUE FORMULA button

  10. Hello Rafael, good afternoon!

    I know that the result is 5, but I do not understand why the result is false, in fact what confused me was the sign of the different ():

    Questão: =SE(CONTAR.SE(A1:C4;”>5″)MAIOR(A1:C4;6) ; MENOR(A2:B3;2) ; MAIOR(A1:B4;3))

    ABC
    1 2 3 4
    2 1 2 8
    3 5 5 6
    4 10 11 12

    From now on, very grateful and congratulations for the excellent content of the blog !!

  11. Good afternoon Rafael

    I am wanting to deploy an alert condition in which when I select the type of responsible "State or Federal" body where for both the term is up to 96 months based on the remaining months column, the alert column brings me a message time for the contract to expire) on the basis of the deadline "start of validity and expiration of the contract", and if you have to insert another condition based on the 96 months, generate an alert every year, such as passed 12 months> generates the alert with message (Passed 12 months of the contract), passed 24 months, generates the alert (Passed 24 months of the contract) and so on until closing the 96 months ... already tried here but I can not. I'm grateful if you can help. Thank you!

  12. Hi Alberto, it is possible yes, just create auxiliary columns to arrive at these values. For example, for the player who has 0,5, you would have to do a calculation with the SE formula to understand if the value is greater than the value it has, just add what it has, if it is less than or equal, add all and then make a sum of all these values

  13. a doubt

    On a poker betting table
    where for example a player has an "all in" value of actual 3
    we have more 5 players with the following values ​​of "all in"
    3
    2,5
    0,5
    1
    3
    In the case of poker, each player will participate in the pot according to his maximum bet, so for example, the player who bet 0,5 will have in his pot the total amount of 3 real, the player who bet 2,5 will have in his pot the value of 11,50

    you can create a formula to calculate this automatically, type sum of cells A1: A7 if the value is equal or less, and if value is greater only the corresponding one of cell A4, for example.
    A formula that calculates the pot value of each player

  14. Hi Pedro, in this case, you can create a table with values ​​in the first column, for example, A1 = 0, A2 = 49999, A3 = 65000, etc. Then, in the second column, you put the respective percentages. Then just do a PROCV by searching the value field, using the table as array, index number column = 2 and TRUE at the end. Very simple and works. I hope I have helped, hugs

  15. Rafael, my name is Pedro.
    I need to make a range for calculating commissions, I have the following situation: smaller than 49999, calculate 5%, greater than 49999 and less than 64999, 5,5%, 65000 to 79999, 6% and higher than 79999 to calculate 6,5%, I already tried several formulas, but I can not. Sorry to send you a response, but I'm desperate.

  16. Hi Eldson, I do not know how to make this kind of formula. If anyone here knows and helps, it would be great!

  17. Good evening.

    Friend thank you for your posts, it has helped me a lot.

    I need a formula that identifies the values ​​between an interlude. ex. 10 and 20, 10 and 20 end, and I present the 10,11,12,13,14,15,16,17,18,19 and 20 values.

  18. Dear Rafael. You have no idea how much you help people. Thank you for me and for all of them. My question is this: I made a spreadsheet where I calculate monthly interest payments compounded by 1% per month. For example, 1000 payable in 50x to 1% per month. So far everything is perfect. But the question is ... How to create a condition whereby only show me possible plots OF AT LEAST R $ 100. Whatever it is, it does not even show. It only gives you as few as R $ 110 for example. Never less than 100 to pay. Thank you very much. You're almost my last hope! lol

  19. I did not understand what you meant Felipe. I recommend you put this question better explained in our forum - forum.luz.vc

  20. In fact, Rafael wanted the cell value to appear, not to direct a hyperlink to this cell, bound to the hyperlink next to it.

  21. You can create a formula that automates this. For example: in A1 you would place the value of the chip and in A2 you would create the formula = SE (A1 = "10 Tab"; "2 Group"; SE (A1 = "11 Tab";

  22. Hi Felipe, it seems like a very good job to do, but it is possible. In Excel you can make the hyperlink to be directed to a specific cell, just see the options inside the command to insert the link

  23. Dude, I need you to help me !! . - I would like to know if it is possible to do the following task, as an autocomplete, I would like when I type itself for example, 10 tab in a cell would automatically appear group 2 in cell forward ...

  24. Dude I could not get in touch with another place ... so I'll try to put my doubt right here ... - next I would like to know if it's possible to do the following task, like an autocomplete, I would like when I type it for example, 10 plug automatically 2 group would appear in the cell ahead ...

  25. Good morning, I really need your help!

    I have an excel document with multiple worksheets, and each worksheet corresponds to a client, and a worksheet is the complete list of these clients, with a hyperlink to each of the corresponding worksheets. In this list of clients, I need a column that refers to a cell in the hyperlink worksheet that corresponds to the row, as if each cell in that column entered the corresponding hyperlink and connected to a standard cell in that worksheet. Is there a condition for this case?

  26. Good afternoon, I would like a light and learn to ascertain this information from a similar base to this, through formulas. In some cases I use PivotTable, but now I would need to use formulas. Thank you in advance.
    1- inform customers purchased by zone (different customers who made purchases, if the customer bought more than 1 instead or bought more than 1 product, consider only 1 purchase);
    2 - Inform by logical formula the (different) customers who made purchases by product. If the customer bought the same product over 1 instead, consider only 1 purchase;
    3 - inform the 5 largest customers who had the most purchases in the period.

  27. To put more than one condition within the same SE it is important to use another formula, which is E. It would look something like this:

    =SE(E(A2>0;A2<=180;0,225;xxxx)

    Note that I've put 0,225 for the percentage because that's how it spelled correctly. If you put it in the way you indicated in your formula, you probably need to enclose it in quotation marks.

  28. Hi Osvaldo, just enter our site http://cursos.luz.vc/ and acquire the course that you are most interested in. We have the beginner + intermediate or advanced modules. There is also the option of a package with the two that has a good discount.

    Any question just tell me

  29. Good morning ... I write from Mozambique, I would like to know what I can do to attend the courses taught> ??

  30. Good afternoon, Rafael.

    I need to establish an SE condition, where the formula allows me to stipulate a range for the cell (eg 01 <X <15), where X is the cell. I tried to = SE (0 <A2 <= 180; 22,5% ...) but not right. How can I by this condition in the formula SE?

  31. Hi Caroline,

    To create formulas you need objective criteria and the colors do not fit these criteria. So if you can define words or values ​​that can be analyzed as conditions will make your life easier to develop this logic

  32. Good morning, Rafael
    I have a question, I want to use the condition form with the words "Done" and "Not completed", but I need to make this formula to be done as follows: If column A is all GREEN (green is the same letter color ) means that it is COMPLETED / If column A is GREEN and BLACK means NOT CONCLUDED.
    How can I make this conditional formula, and what I have to use are the colors?

  33. Hi Edirley, assuming the note is in cell A1, would be something like

    = SE (A1> = 7; "Approved"; do the calculation here)

  34. Good morning, Rafael.
    I have a question in a form of excel, I need to create a field in which I need to calculate the grade that the student needs to take on the exam, but this is conditioned to average. If the mean is> 7 would not have to perform the calculation returning the approved word, and that calculation would only be necessary if the grade was <7. How can I do this. Thank you

  35. Thank you for the information, but I do not think I explained it well.

    Example:
    = SE ('GENERAL LIST'! C4: C93 = "x"; 'GENERAL LIST'! B4: B93; "")

    Where column C represents an activity; "X" represents the marking of the activity; B represents the list of students.

    The aim is to: form a new column, in a new sheet, with the list of the students that marked activity, that is, those that have "x" in column C.

    The problem is that the new column has a limit number of rows. The idea is that the column only have the names of those who marked the activity and that the blank lines (test_se_false) cease to exist.

    My idea is to come up with a formula in which the test_is_false refers to the next student's logical test.

    Thank you

  36. Hi Silvinha,

    I do not know if I understand it very well, but I believe that instead of the "" it is only you put the reference to the cell with the name or that you test the validation. So whenever it's fake it will return the same value and move to the next line

  37. Good afternoon,

    Regarding the SE Function:

    I want to make a list of names that meet the "logical test".

    Since not all names meet "logical test" and "value_if_false" equals "", of course, white space appears in the list.

    To eliminate these blank spaces, what condition should I put in "value_se_false" when I want the "logical_test" to go to the next cell?

    Thank you!

  38. Hi Mari,

    just use the SE function normally. Assuming the notes are in the B2, C2, D2 cells, and the final note is in the E2 cell. In the E2 cell you would put the following function:

    =SE(SOMA(B2:D2>10;10;SOMA(B2:D2)

    Then just drag down with fill handle

  39. Good afternoon
    I'm a teacher and I'm doing a spreadsheet. I need two different, independent functions in the same formula. The value of the column in question is the sum of three other columns, except that these total values ​​can not exceed the maximum 10. Then the notes larger than 10 must be equal to 10. Help me?
    hug
    Mari

  40. Hi Diego,

    I think the error is in the sign of> =. I believe the correct one would be = SE (G2 <= H2; "Swap"; "")

    So you tell Excel that if G2 is smaller, that is, the current day is larger, the exchange should happen

  41. Good morning, Rafael, how are you? Very good your tips.

    I have a doubt in a worksheet and I could not solve it.

    I need the table to be changed every six months. But in my case it is happening that if I put the date today, it already shows that it needs to change. Could you kindly tell me how to solve it?

  42. You're welcome, Jessica! Thank you for the words. I'm glad we've helped you and if you have any other questions regarding Excel, just talk to us at forum.luz.vc

  43. Hi Abraham, how are you?

    You can solve various types of problems in Excel. From mathematical, logical, to textual and financial issues.

    Excel has several tools that help you work with numbers, texts, databases. If you have a problem that you want to solve with Excel, tell us what I'm talking about if it's possible to solve it.

    Hugs

  44. Hi Diego, how are you?

    thanks for the compliment. Good to know that you are interested in knowledge of Excel, soon (still this month) we will launch a platform of courses focused on Excel. The first course is the basic and intermediate (I imagine it is not for you), but if you want to comment on what the next ones would be, I'd really like to know what you think, just answer me here

  45. Hi Diego, how are you?

    thanks for the compliment. Good to know that you are interested in knowledge of Excel, soon (still this month) we will launch a platform of courses focused on Excel. The first course is the basic and intermediate (I imagine it is not for you), but if you want to comment on what the next ones would be, I'd really like to know what you think, just answer me here.

LEAVE AN ANSWER

Please, write your comment
Please enter your name