SE Function Nested?

Navigation
4

I would like to know how to nest the function SE as follows:

  

Se G6 for >=20 e <=30 então 100, Se G6 >30 e <=40 então 200, Se G6 >40 e <=50 então 300, Se G6 >50 e <=60 então 400

How to mount this function?

    
asked by anonymous 23.01.2017 / 15:09

2 answers

5

You should use the fuction E to join more than one condition. So, one possible answer to what you want is:

  

=SE(E(G6>=20;G6<=30); 100; SE(E(G6>30;G6<=40); 200; SE(E(G6>40;G6<=50); 300; SE(E(G6>50;G6<=60); 400; "VALOR INVÁLIDO"))))

Note that the last check was missing you set the value for the SIGN. I put "VALOR INVÁLIDO" , but you should set some value that makes sense in your problem domain.

Another suggestion, more "facilitated" in the sense that it uses only one OU " to check at first if the value is in the valid total range and then simply checks the threshold limit, it was suggested by the colleague @Bacco:

  

=SE(OU(G6<20;G6>60); "VALOR INVÁLIDO"; SE(G6>50; 400; SE(G6>40; 300; SE(G6>30; 200; 100))))

IMPORTANT:

Make sure your real intention would not actually make some calculation about the value. Because there you greatly facilitate your life by avoiding this many conditions.

In your formula, the only inclusive lower bound - that is, checked with >= instead of just > - is 20. Repair:

  • the 20 numbers, 21, 22, ..., 30 result in 100
  • the numbers 31, 32, ..., 40 result in 200
  • the numbers 41, 42, ..., 50 result in 300
  • the numbers 51, 52, ..., 60 result in 400
    • Assuming that this is wrong (and that 20 should not be included in the range of 100), you can get this same calculation by multiplying the digit from the previous decade (subtracted from 1 or 2, depending on whether or not it is divisible by 10) per 100. The formula would look like this:

      

    =SE(MOD(G6;10)=0; TRUNCAR(G6/10)-2; TRUNCAR(G6/10)-1) * 100

    The first part checks ( SE ) the rest ( MOD ) of the division by 10 is 0. If it is, the number is 20, 30, 40, and so on. In this case, it's the upper bound, so you get the whole part ( TRUNCAR ") of the division by 10 and subtract 2. If not, take the whole part of the division by 10 and subtract only 1. Then only multiply the value returned by 100 to get the result of your interest.

    If 20 is even part of the first group, then that calculation does not work. Implementing boundary conditions in it would only give more work, and maybe even compensate leaving the original rule with different conditions. I'm just putting this example to you reflect if your problem is not actually a calculation rather than a to-type table (which, incidentally, can also be implemented using the table search functions ).

        
    23.01.2017 / 15:14
    1

    If I understand correctly, it will look like this. 

    =SE(E(G6>=20;G6<=30);100;SE(E(G6>30;G6<=40);200;SE(E(G6>40;G6<=50);300;SE(E(G6>50;G6<=60);400))))

    Remembering that if G6 is less than 20 or greater than 60 the result will be FALSE.

        
    23.01.2017 / 15:18
    Where to save API connection credentials on Android What is the purpose of flow control to continue? [duplicate]