Ask Your Question

How to avoid multiple nested "IFs"?

asked 2020-10-17 17:00:31 +0100

elnath78 gravatar image

I need to implement a check on the result of two cells, like if this then that, else if this then that, etc.. but I'd do it in an ordered way, I cannot put the result of the two cells into another cell. Is it possible to have (in a function) a switch/case that evaluate the same condition A1+A2 and then performs the switch based on that, like >0 or >10 etc..

edit retag flag offensive close merge delete


Are you looking for =IFS(A1+A2>10;<this>;A1+A2>5;<that>;A1+A2>0;<another>:1;<defaultresult>) (the first TRUE wins, so take care of the order)?

Opaque gravatar imageOpaque ( 2020-10-17 17:14:33 +0100 )edit

@Opaque I wanted to enter A1+A2 only once to reduce the code. However I think I resolved creating named areas so stripped out the sheet/cell references and code is more readable now.

elnath78 gravatar imageelnath78 ( 2020-10-20 13:17:22 +0100 )edit

2 Answers

Sort by » oldest newest most voted

answered 2020-10-17 17:43:27 +0100

keme gravatar image

updated 2020-10-17 20:37:29 +0100

If it is important that the expression be evaluated only once, and the result is a number in the range 1-254 (or can be transformed so that each "if-match instance" is a unique number within that range), the CHOOSE() function may be what you are looking for.

LOOKUP() with two inline arrays is the other option I can see for a single evaluation "one liner" without any helper cells. Inline lookups easily grow unmanageable, so if you can use "helper" cell ranges I'd suggest you set up a lookup table for value/result pairs instead, and use some lookup function.

With more detail about what you are trying to accomplish and why you are constrained to a single cell, it would be easier to provide specific advice. There may be other ways to approach it.

edit flag offensive delete link more


I was looking for something function(target;eval1;result1;eval2;resul2;eval3;result3 thant in my case can be come something like function(a1+b1;">0";a1+b1;">10";a1;">50";b1;"<>51";a1*b1) so far count.ifs seems a good alternative but with that I cannot have OR operators as I dont with IFs.

elnath78 gravatar imageelnath78 ( 2020-10-19 12:29:00 +0100 )edit

Hello @elnath78

To simulate a (Reason1 OR Reason2) => Result1, you may write Reason1 => Result1; Reason2 => Result1

Kind regards, Michel

mgl gravatar imagemgl ( 2020-10-19 12:43:13 +0100 )edit

Your sequence of operations does not make sense to me. Logic walkthrough as I read it:

  • If sum is positive, return A1+B1
  • If sum is above 10, return A1
    A result above 10 means a positive result which is already handled by the first condition.
  • If sum is above 50, return B1
    Again, a result >50 would be caught by either of the two previous conditions.
  • If sum is different from 51, return the product.
    All positive values are already handled, and every negative value will be different from 51, so that test is superfluous.

So, rather than explaining how you believe that the function should look like, tell us what you need to accomplish. Perhaps like so:

  • When sum of A1 and B1 is negative, return zero
  • When sum is between 0 and 10, return the sum
  • When sum is between 10 and 50, return A1
  • When sum ...
keme gravatar imagekeme ( 2020-10-19 13:47:19 +0100 )edit

@mgl it was just an example not what I'm trying to do right now.

elnath78 gravatar imageelnath78 ( 2020-10-20 13:18:02 +0100 )edit

answered 2020-10-17 17:19:14 +0100

mgl gravatar image


Have a look at IFS

Kind regards, Michel

edit flag offensive delete link more


Ifs is what I wante to avoid. Also it does'n allow OR operator.

elnath78 gravatar imageelnath78 ( 2020-10-19 12:23:56 +0100 )edit

Also it does'n allow OR operator.

@elnath78 - What do you mean by that? Anything resulting in a TRUE / FALSE is allowed as a condition: IFS(OR(A1>1;B1>5;); A1+B1;1;1) is a valid IFS() using OR(), so your comment is not clear to me.

Opaque gravatar imageOpaque ( 2020-10-19 19:55:31 +0100 )edit

@Opaque to handle multiple cases IF returns true if all conditions are true. I need different behavior by different case, IFs have a static possible result.

elnath78 gravatar imageelnath78 ( 2020-10-20 13:19:26 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-10-17 17:00:31 +0100

Seen: 40 times

Last updated: Oct 17