How to avoid multiple nested "IFs"?

[elnath78]

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…

[anon73440385]

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)?

[elnath78]

@anon73440385 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.

[keme]

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.

[elnath78]

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.

[mgl]

Hello @elnath78

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

Kind regards, Michel

[keme]

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 is exactly 51, return B1
• When sum is some other value above 50, return the product A1*B1

Note that I am guessing here. Please review!

[elnath78]

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

[mgl]

Hello

Have a look at IFS

Kind regards, Michel

[elnath78]

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

[anon73440385]

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.

[elnath78]

@anon73440385 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.