Function IF Nested?

Asked

Viewed 516 times

4

I wonder 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 assemble this function?

excel

2 answers

5


You must use the fução E to make the union of more than one condition. Thus, a possible response 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 in the last check you missed setting the value for the ELSE. I put "VALOR INVÁLIDO", but you should define some value that makes sense in your problem domain.

Another suggestion, more "facilitated" in the sense that it uses only one OU to check right from the start if the value is in the valid total range and then simply checks limit, 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:

Check whether your real intent would not actually make any calculation about the value. Because there you greatly facilitate your life by avoiding that lot of conditions.

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

  1. the numbers 20, 21, 22, ..., 30 result in 100
  2. numbers 31, 32, ..., 40 result in 200
  3. numbers 41, 42, ..., 50 result in 300
  4. numbers 51, 52, ..., 60 result in 400

Assuming this is wrong (and that 20 should not be included in the range of 100), you can get that same calculation by multiplying the digit of the previous ten (subtracted by 1 or 2, depending on whether or not it is divisible by 10) by 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) division by 10 is 0. If it is, the number is 20, 30, 40, etc. In this case, it is the upper limit, then you take the whole part (TRUNCAR) division by 10 and subtract 2. Otherwise, take the entire part of the division by 10 and subtract only 1. Only then multiply the returned value by 100 to have the result of your interest.

If 20 is actually part of the first group, then that calculation doesn’t work. Implementing boundary conditions in it would only take more work, and perhaps even make up for leaving the original rule with different conditions. I’m just setting this example for you reflect on if your problem is not actually a calculation instead of a-to-type table (which, by the way, can also be implemented using the table search functions).

  • Thank you very much. I used the first function proposed, and it worked in part. Take a look at my function: =SE(G2<=117,12;"500,00";SE(E(G2>177,12;G2<=184,4);498;SE(E(G2>184,4;G2<=312,33);350;SE(E(G2>312,33;G2<468);315;"SD")))) within this function the values that are entered in the second logical test (G2>117,12;G2<=184,40) are returning "SD", and should return "498,00". What should I do? grateful.

  • Not at all. Well, there’s something wrong there. Your formula is much strange: it compares values but leaves intervals between them (type 117,12 x 177,12), it returns different types of data (either string, or number). Also, you do not mention what is the value that is in G2. Should not even return "SD"? I mean, it seems like a lack of attention on your part. See the graphic representation of its formula (it is very useful to draw like this): http://imgur.com/a/BuZiE The path in bold is what is being done, if the return is "SD". Evaluate where you made a mistake.

  • 1

    OK. I re-did the job and everything worked out. Thank you very much.

  • Again, for nothing. If the answer helped you, please consider marking it as accepted.

  • Please allow me to ask you something else, if my formula were the following: IF H2=100.00 returns the value 10.00, IF H2=200.00 returns the value 20.00, IF H2=300.00 returns the value 30.00, IF H2=400, return the value 40, if not "SD". How would you put that function?

  • Now, exactly as you describe it. It’s just "nesting" (putting one inside the other) the commands SE remembering that it works like this: SE(<condição> ; <valor se condição for verdadeira> ; <valor se condição for falsa>). (Although in this case you are basically dividing the value of H2 by 10, no? That is, your easiest formula is simply: =H2/10). Ah, this site is not a forum. If you still have questions about "how does the Excel SE command work", I suggest you open a new question, ok?

Show 1 more comment

1

If I understand correctly, it’ll stay that way.

=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 in the case of G6 is less than 20 or more than 60 the result will be FALSE.

  • Thank you. everything worked out.

Browser other questions tagged excel

You are not signed in. Login or sign up in order to post.